2

I have one table that imported from shp file (projection - wgs84- polygon).

The table (locations_list)has 5 fields: id, geom, pin, shape_star, shape_stle, isEmpty.

I created spatial index on the table with a geometry column (geom)

pin is the location id and it is unique.

I have more than 15k locations(pin) and each location either empty or not(isEmpty takes values 0 or 1) I want to count for each location(pin) in the table how many empty location around it within 500 feet. I used the following query to do that:

Create table as count
Select a.pin
sum(a.isEmpty) as count
from locations_list a
join locations_list b on a.pin != b.pin
and
ST_Dwithin(a.geom::geography,b.geom::geography,152) 
group by a.pin

This query is under testing and it takes hours to complete. Is there any advice to improve the performance?

1

A few things, first if you're only counting the empty locations within 500 feet, just select the empty locations, and use count(*) not sum(isEmpty). isEmpty should be a bool: ALTER TABLE locations_list ALTER TYPE isEmpty bool; This is what your select should look like,

SELECT a.pin, count(*)
FROM locations_list a
JOIN locations_list b
  ON b.isEmpty
  AND a.pin != b.pin
  AND ST_Dwithin(a.geom::geography,b.geom::geography,152)
GROUP BY a.pin;

Second, you're casting both a.geom and b.geom to geography?

ST_Dwithin(a.geom::geography,b.geom::geography,152)

If they're not already geography just use geometry. Check ST_Dwithin In fact, it's likely faster to cast to geometry, if needed, to do this.

For Geometries: The distance is specified in units defined by the spatial reference system of the geometries. For this function to make sense, the source geometries must both be of the same coordinate projection, having the same SRID.

That said, if you're going to play around with this, you may want to check out the last argument, use_spheroid, to ST_Dwithin,

For geography units are in meters and measurement is defaulted to use_spheroid=true, for faster check, use_spheroid=false to measure along sphere.

Also when you say you have an index, do you have a btree index on a.pin, and a GIST index on locations_list.geom? You should also consider CLUSTERING on locations_list.geom. Just to make sure, did you VACUUM ANALYZE after you loaded the data?

It may be worth stressing this grows exponentially. So it's count(locations_list) ** 2 . If you've got 15k rows, that's 225M comparisons: it's not going to be fast. Look at cutting the set in half first (if possible). That's how this is normally done.

1

When you cast your geometry to geography you lose any benefit you might get from having a spatial index on geometry. You need to decide: are you going to work in geometry (and maybe choose a good planar projection for your data) or in geography. In the long run, if you have working data in a constrained area, picking a planar projection will reward you with more functions supported by default and higher performance. But let's ignore that for now and just move to geography.

ALTER TABLE locations_list ALTER COLUMN geom TYPE geography USING geography(geom);
ALTER TABLE locations_list RENAME COLUMN geom TO geog;
CREATE INDEX locations_list_geog_x USING GIST (geog);

SELECT a.pin, count(*)
  FROM locations_list a
  JOIN locations_list b
    ON ST_Dwithin(a.geog, b.geog, 152)
   AND a.pin != b.pin 
 WHERE b.isEmpty
 GROUP BY a.pin;
  • You don't necessarily have to switch to the geography type; you can create a geography index while retaining the geometry type with the following: CREATE INDEX ON locations_list USING GIST(geography(geom)) – dbaston Dec 4 '16 at 19:20
  • As long as you switch your casting syntax to geography(geom), yes. But I like the simpler end-state of just saying what you mean and meaning what you say. – Paul Ramsey Dec 4 '16 at 21:15

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.