1

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

opensas
  • 275
  • 3
  • 9

1 Answers1

2

This is what finally worked ok:

select *
from my_contacts where
st_distance(
  the_geom,
  st_GeomFromText('POINT(-58.3778105 -34.6077041)', 4326), true
) < 500

This query correctly returns me all the points within 500 metres from lon: -58.3778105, lat:-34.6077041

opensas
  • 275
  • 3
  • 9