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.

Answer 1

st_transform will allow you to transform to other SRID, give it a look up on the web. 4326 is most common..sorry, lacking time to fully write an answer, will edit in a bit.

edit..just to confirm 'the_geom' in your example is a polygon or multipolygon?

Answer 2

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.