1

I have a GeoJSON polygon, and a table with lots of points. I want to SELECT all points that are within an arbitrary GeoJSON polygon.

How can I do that with PostGIS SQL?

  • 1
    What do you mean with GeoJSON polygon? Do you have that saved into a file on your hard disk or what? – user30184 Aug 3 '15 at 11:25
  • I have it as a JSON object. – knutole Aug 3 '15 at 11:27
6

You need a ST_GeomFromGeoJSON

select * 
from points p
where ST_Within(
    p.geom,
    ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[...]}')
)

Edit: ST_Intersects works also (need to set SRID on GeoJSON, and transform):

select row_to_json(t) 
from (select * from table 
where st_intersects(
    st_transform(
        st_setsrid(
            ST_geomfromgeojson('{"type":"Polygon","coordinates":[...]}'),
        4326), 
    3857), 
table.geom)) as t;
  • Ansewer is on point :)... But you may be interrested to know there is a JavaScript library for processing Geojson turf.js - github.com/Turfjs/turf. If you are considering for a web based implementation – user33290 Aug 3 '15 at 12:47
  • How can I do a subquery on this also? ie. instead of FROM select * from table, I want to do FROM (select * from table where height>1500) as sub - but this doesn't work. Why? – knutole Aug 3 '15 at 14:31

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.