[GIS] How to intersect a point with a shape

postgispostgis-2.0

I have a table in Postgresql / PostGIS containing county geometries. This table was created by importing a shapefile. I verified the geometry was imported successfully by importing the table into QGIS to view the polygon positions and ensure everything was drawing correctly. So, now I have geometry in a table.

Now, I want to write a SQL query to return the polygon that a points intersects. For example, given a lat/lon of 35, 80 it should return the row for the county that contains the point at 35, 80.

The geometry is in the database with SRID EPSG:2810, "NAD83(HARN) / Minnesota North". The point is in WGS84 – typical coordinates taken from a GPS receiver.

How do I select the row that the point intersects with? The geometry in the GEOM column of type geometry.

Here's a query I started but it doesn't return any rows:

SELECT *
FROM county
WHERE ST_Intersects(
        geom,
        ST_GeometryFromText('SRID=4326;POINT(47.000000 -91.000000)')
        );

Best Answer

As Vince pointed out:

SELECT *
FROM county
WHERE ST_Intersects(
        geom,
        ST_Transform(ST_GeometryFromText('POINT(-91.0 47.0)', 4326), 2810)
        );

You can also use ST_MakePoint() if that would be easier with your source than constructing the WKT.

Related Question