How do I intersect a point with a shape?
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)') );
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.