Querying PostgreSQL with GIS to get GeoJSON for GeoJSON tile layer?

Question

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.

Answer 1

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.

Answer 2

This is how I would form the query you're asking about using ST_MakeEnvelope() and the && operator.

You can request whatever field data you want, in addition to the geometry in GeoJSON format using ST_AsGeoJSON(). Also I recommend taking advantage of that function's ability to specify a max decimal precision value. Here I'm requesting geometries with 7 decimal places.

If you request additional data fields, you'll need your PHP to arrange the data returned by the query into JSON before returning it to your client.

SELECT 
    gid,
    zip,
    zip_n,
    city,
    ST_AsGeoJSON( geom, 7 ) as geojson
    -- "7" is max number of decimal places in return geometries
FROM 
    post_pl
WHERE
    geom && 
    ST_MakeEnvelope(-81.0803031, 34.0605881, -81.0240411, 34.0931839, 4326);
    -- ST_MakeEnvelope( sw_lon, sw_lat, ne_lon, ne_lat, SRID )

Now, that's just a raw answer. I still say there is a big "gotcha" you'll need to reconcile. Some of your polygons are guaranteed to intersect with multiple tile boundaries. Without accommodating for this, you'll be 1) requesting duplicate (maybe even tripled or quadrupled) geometries from your db, 2) returning those redundant geometries over the wire, and 3) rendering those redundant geometries in your map.

Unfortunately, I don't have an elegant solution to that problem. But if you have the time (queue laugh track)--as I suggested in a comment--you could write a stored procedure or perhaps a Python script that builds a lookup table where every {z}{x}{y} record is an array of gid values corresponding to the unique geometries that should be rendered for that tile. With a little luck, perhaps someone will comment on how they've handled this problem.