3

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.

  • Welcome to the community. One thing I wonder--neighboring tile areas will certainly have some shared polygons. So unless you don't mind duplicating their transfer over the wire and their subsequent rendering in your map, you'll want a strategy for dealing with the redundancy. If you give the polys some fill opacity, you should see the duplicated features as being darker than the non-duplicated features. ..unfortunately I don't have any good advice for how to handle that issue. I always do one big map-area select and just deal with it. haha – elrobis Apr 10 '15 at 20:48
  • hmm.. one thing that might work.. I'm just spit-balling here. But you could write a script to iterate over the levels/tiles in your area of interest and build a sort of lookup table containing the gid values for the features you wanted represented in every potential tile position {z}{x}{y}. The goal of the script would be to keep track of all the features in your dataset for every zoom level and allocate them to the {x}{y} ranges they fell into, and then DENY them from neighbor ranges if they've been previously allocated. (Plus, I think the lookup would be stupid-fast.) – elrobis Apr 10 '15 at 20:55
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.

1

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.

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.