1

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)')
        );
  • It's not surprising that a search one degree below the South Pole doesn't return any data, even if that data was in GCS_WGS_1984 (which it isn't). It may help if you stop thinking "lat/lon" and start thinking "lon,lat". But you'll also need to do NADCON and reprojection (a search on "ST_Transform" would be appropriate) – Vince Sep 23 '15 at 2:33
3

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.

  • 1
    There was some fine print in the man page on how to use PROJ.4 options for NADCON to convert the WGS84 to NAD83 during transform. – Vince Sep 23 '15 at 10:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.