Wednesday, 30 March 2016

srid - cartodb: query for the points within a radio from another point - postgis


I've georreferenced a couple of points in a CartoDb table, so now I have a the_geom column of type point


I'd like to get all the points that are x meters away from a certain x,y point.


So far now, following this article http://unserializableone.blogspot.com.ar/2007/02/using-postgis-to-find-points-of.html, I tried the following:



SELECT * FROM my_contacts
WHERE
distance(
transform(PointFromText('POINT(-34.6043183 -58.380924)', 4269),32661),
the_geom
) < 1000

(in this case my x,y point would be -34.6043183 -58.380924 and I'm looking for locations within 1000 meters from it)


and I get this error: ERROR: Operation on two GEOMETRIES with different SRIDs


I tried without the transform, and I get the same error



How can I find out the SRIDs of the points in my db?


And how can I translate a lat, lon google map point to that desired SRID?


update:


with this query I found out the SRID of my points


SELECT ST_SRID(the_geom) FROM my_contacts

they are all 4326, so I tried with


and this seems to work rather ok:


SELECT *
FROM my_contacts

where
distance(
transform(PointFromText(
'POINT(-34.6675645 -58.3712721)', 4326),4326),
the_geom
) < 33.62
order by distance

but I just got thiw 33.62 by trial and error, I don't know how to translate it to meters, and the results when trying with a different point don't seem to be very consistent...


thanks a lot





No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...