0

I'm currently trying to pull neighborhood data from Zillow's Neighborhood page (http://www.zillow.com/howto/api/neighborhood-boundaries.htm) and then load it onto a leaflet map, openstreetmap. I created a "Neighborhood" table and added the data into PostGIS using shp2pgsql. Everything works great, I can query the data and get back GeoJSON using ST_AsGeoJSON, however the query is really slow. I'm considering how I could speed up this process as it looks like ST_AsGeoJSON is the primary culprit. This table will not be updated that often, so I'm wondering if it would make sense to simply store the GeoJSON in another column within Neighborhood and then return that instead of converting the geom column every page refresh.

Here's an example of my Neighborhood table.

CREATE TABLE "neighborhood" (gid serial,
"state" varchar(2),
"county" varchar(43),
"city" varchar(64),
"name" varchar(64),
"regionid" numeric);
ALTER TABLE "neighborhood" ADD PRIMARY KEY (gid);
SELECT AddGeometryColumn('','neighborhood','geom','0','MULTIPOLYGON',2);
1
  • 1
    Welcome to GIS.SE. Can you show what the query looks like?
    – BradHards
    Dec 29 '14 at 0:21
3

@BradHards' inquiry led me to investigate how my ORM was executing the query. Instead of applying ST_AsGeoJSON() to the column, I was inadvertently applying the function to each data record, thereby adding an extra query for each record returned. After I manually executed the query, everything worked much faster!

BEFORE:

SELECT neighborhood.gid AS neighborhood_gid, neighborhood.state AS neighborhood_state, neighborhood.county AS neighborhood_county, neighborhood.city AS neighborhood_city, neighborhood.name AS neighborhood_name, neighborhood.regionid AS neighborhood_regionid, ST_AsBinary(neighborhood.geom) AS neighborhood_geom 
FROM neighborhood 
WHERE neighborhood.state = %(state_1)s AND neighborhood.city = %(city_1)s
INFO sqlalchemy.engine.base.Engine {'city_1': 'Detroit', 'state_1': 'MI'}

This next query was activated for every record I was returning, causing the long query time:

SELECT ST_AsGeoJSON(ST_GeomFromWKB(%(ST_GeomFromWKB_1)s, %(ST_GeomFromWKB_2)s), %(param_1)s) AS "ST_AsGeoJSON_1"
INFO sqlalchemy.engine.base.Engine {'param_1': 4, 'ST_GeomFromWKB_2': -1, 'ST_GeomFromWKB_1': <memory at 0xb5b60cc4>}

AFTER:

SELECT neighborhood.name AS neighborhood_name, ST_AsGeoJSON(neighborhood.geom, %(param_1)s) AS "ST_AsGeoJSON_1" 
FROM neighborhood 
WHERE neighborhood.state = %(state_1)s AND neighborhood.city = %(city_1)s
0

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.