4

I'm new to postgis and I'm trying to show that a point intersects a polygon.

However, no matter how hard I try I can't.

Here is the point: 0101000020E6100000000000009E2FC4BF629EA494D7BB4940

Here is the polygon: https://mapit.mysociety.org/area/65731.wkt

I'm trying:

SELECT st_contains('0101000020E6100000000000009E2FC4BF629EA494D7BB4940',geom), name FROM table;

Where geom is the column with all the polygons.

As a note, the polygons are multipolygons in the database.

What am I doing wrong?

6

The Point and the Polygon have different coordinate systems:

The Point in WKT:

POINT(-0.157703161239624 51.4675164989283)

These are Lat/Lon Coordinates (maybe EPSG:4326). The Polygon has a different coordinate system:

POLYGON((526640 176973 [...]))

It seems to be EPSG:27700 (OSGB 1936 / British National Grid)

So your correct call would be:

SELECT 
  st_intersects(
    ST_Transform('0101000020E6100000000000009E2FC4BF629EA494D7BB4940'::geometry, 27700),
    geom
  ),
name
FROM table;

OR you could use the API to get EPSG:4326 polygons: https://mapit.mysociety.org/area/4326/65731.wkt

enter image description here

  • I tried SELECT st_contains(ST_Transform('0101000020E6100000000000009E2FC4BF629EA494D7BB4940', 27700),geom), name FROM table; but it has the error Error: function st_transform(unknown, integer) is not unique. Any ideas? – Bill Apr 20 '17 at 13:58
  • you might need to cast the string as geometry => '0101000020E6100000000000009E2FC4BF‌​629EA494D7BB4940'::geometry. I updated the answer. – pLumo Apr 20 '17 at 14:07
  • It's still resulting in no results. I downloaded the dataset from here ordnancesurvey.co.uk/business-and-government/products/…. Then imported with shp2pgsql westminster_const_region.shp public.westminster > areas.sql and then run the above query and I don't get anything. – Bill Apr 20 '17 at 14:50
  • Also, when I query SELECT ST_SRID(geom), name FROM westminster; then the SRID of the imported geom is 0 – Bill Apr 20 '17 at 14:53
  • Update the SRID of the geom column: SELECT UpdateGeometrySRID('westminster','geom',27700); – pLumo Apr 20 '17 at 14:54

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.