[GIS] Querying PostgreSQL with GIS to get GeoJSON for GeoJSON tile layer

geojsonlayersleaflet

I have a PostgreSQL database with one table like the following:

CREATE TABLE post_pl
(
  gid serial NOT NULL,
  zip character varying(5),
  zip_n integer,
  city character varying(50),
  geom geometry(MultiPolygon),
  CONSTRAINT post_pl_pkey PRIMARY KEY (gid)
)

In this table there are stored polygons as geometry.

I want to get all polygons from that table within a bounding box.
I use an htaccess file to route all requests to one PHP file.
Then I use params of the url to create a bounding box.

Example:
URL: polygons.php/{z}/{x}/{y}.json

Creating bounding box(see: osm slippy tilenames):

function tileCoord2BBox($url)
{
    $parsed = explode("/",$url);

    $z = $parsed[3];
    $x = $parsed[4];
    $y = $parsed[5];

    $n = pow(2, $z);

    $lon1 = $x / $n * 360.0 - 180.0;
    $lat1 = rad2deg(atan(sinh(pi() * (1 - 2 * $y / $n))));

    $lon2 = ($x + 1) / $n * 360.0 - 180.0;
    $lat2 = rad2deg(atan(sinh(pi() * (1 - 2 * ($y + 1) / $n))));

    $bbox =  array();
    $bbox['lon1'] = $lon1;
    $bbox['lat1'] = $lat1;
    $bbox['lon2'] = $lon2;
    $bbox['lat2'] = $lat2;

    return $bbox;
}

I used those return value in ST_MakeEnvelope to limit the results.

What i want to know is:
How should a postgres statement look like to get all polygons within those
bounding box and that I can use the result as GeoJSON Tile Layer like this one: GeoJSON Tile Layer?

If possible with a JSON attribute that I can identify each polygon by "zip" on client side.

I want to have a similiar one like that one in the link above with my own geodata in Leaflet.

Best Answer

You could probably convert your bounding box to a rectangle using ST_MakePolygon() and use ST_Contains() to check whether the polygons are contained within the bounding box. While returning, if you want GeoJSON, just use ST_AsGeoJson() Before doing this, if you have a big table, you might want to index your geometry column as GIST. That should speed up your query.

Related Question