Friday 4 January 2019

Selecting by bounding box with PostGIS


I have a large dataset in a PostGIS database. I am viewing it via a web interface that loads the data as GeoJSON. There is too much data to give the client all at once (10s of MBs worth...) so I would like to get an area of data within the bounding box formed by the edges of the window (the dotted line in the image below). Getting the coordinates for points f and g is easy.


Question 1: Is this a good way of doing things? Should I be thinking of caching them as tiles or is this method likely to be efficient enough?


Question 2: How do I retrieve just the data within this bounding box?


Question 3: If a shape overlaps the edge of the bounding box (e.g. shape A below) is there a simple way to crop it as it is queried like in the second image?


enter image description here



Answer




just change the order of the questions and you have a procedure:


2)Select only what intersects the bbox (ST_Intersects).
3)Intersect it with the bbox to clip the polygons (ST_Intersection).
1)Create a new table with the results (CREATE TABLE newtable AS SELECT...).


ST_Intersection docs contain a code sample. You will need to adapt it and use ST_PolygonFromText as the bbox.


No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...