Find all geometry that intersects a point

Question

I'm trying to find a way to locate all geometry that intersects a given point with PostGIS on CartoDB.com (cloud hosted spatial db).

The closest I've been able to get to reproduce this is:

SELECT * FROM sf_blocks WHERE ST_Contains(the_geom, ST_GeomFromText('POINT(-122.44107 37.750066)'));

Unfortunately this errors out with 'ERROR: Operation on mixed SRID geometries.'

What is the correct syntax to select geometry from a table that intersects with a point? My table, sf_blocks are all polygons.

It turns out I did not have the SRID, which is required. To clarify, 'the_geom' is a polygon. — nym

Micha Micha · Accepted Answer · 2012-05-13T17:52:19

The function ST_GeomFromText takes a second argument - the SRID. So if your sf_blocks layer is in Lon/Lat, WGS84 then the EPSG code is 4326. In this case

SELECT * 
FROM sf_blocks 
WHERE ST_Contains(
    the_geom, 
    ST_GeomFromText('POINT(-122.44107 37.750066)', 4326)
);

should do it. If the sf_blocks layer is in some other Coordinate System, (and the point coordinate seems to be Lon/Lat) then you'll want to use ST_Transform around the GeomFromText part.

Find all geometry that intersects a point

2 Answers