2

I'm trying to do a spatial join on a PostGIS table that has a bunch of hexes and each hex has an ID. I have a separate table withs points in lats and longs and I want to know which hex each point belong in. For instance (points are made up)

 id     lat    long
 1     -83.1    24.5  
 2      55.9   -90.2
 3      16.7    23.4

to

id      lat     long   hexid
 1     -83.1    24.5     5
 2      55.9   -90.2    87
 3      16.7    23.4    23

The problem I have is that I have alot of points (over 100 million) and doing the spatial join is taking DAYS. I have broken the query up into special geographic regions and running them concurrently, but is there anything I can do to my query that will improve my performance? My query is below:

CREATE TABLE newtable AS select id, lat::double precision, long::double precision

ALTER TABLE newtable ADD Primary key (id);

SELECT AddGeometryColumn ('public','newtable','geom',4326,'POINT',2);

UPDATE newtable SET geom = ST_SetSRID(ST_MakePoint(long, lat) ,4326);

ALTER TABLE newtable ADD COLUMN hexid numeric;

UPDATE newtable set hexid = hextable.hexid from hextable WHERE  ST_Within(newtable.geom, hextable.geom);
  • does the hex table have a spatial index? – DPSSpatial Oct 15 '15 at 21:04
  • I'm actually not sure. How can I check? – Minh Mai Oct 15 '15 at 21:06
  • Using PGAdmin, expand your hex table properties, expand the Indexes node, and you should see something there with '..._geom_...' in the name - if you don't have a spatial index built, use some sql like: CREATE INDEX "hextable_geom_spindex" ON "hextable" USING gist (geom); – DPSSpatial Oct 15 '15 at 21:12
  • Gotcha so I found out that I don't have an index for the tables. What is the performance increase I can expect to get off of creating an index? – Minh Mai Oct 15 '15 at 21:20
  • Huge! Go for it and see for yrself! – DPSSpatial Oct 15 '15 at 21:24
4

I'd say you're lacking a spatial index on your point table.

To check, Using PGAdmin, expand your point table properties, expand the Indexes node, and you should see something there with '...geom...' in the name

If you don't have a spatial index built, use some sql like:

CREATE INDEX "pointtable_geom_spindex" ON "pointtable" USING gist (geom);

...and it probably wouldn't hurt to have an index on your hex table too...!

  • So do I need to have the index on the hex table and the new table or should I only have one? Would having two indexes be faster than one? (Sorry I'm new to this) – Minh Mai Oct 15 '15 at 21:39
  • Changed my answer to state that you should definitely have one on your point table, but it won't hurt to have one on your hex's either... – DPSSpatial Oct 15 '15 at 21:41
  • Hrmm doing some research, I'm curious to know if the SELECT AddGeometryColumn ('public','newtable','geom',4326,'POINT',2); statement will already create the spatial index. Do you know? – Minh Mai Oct 15 '15 at 21:48
  • I don't think so... postgis.net/docs/AddGeometryColumn.html but you can create it easily after with the SQL I gave above... – DPSSpatial Oct 15 '15 at 21:53
  • I believe using ST_Intersects or ST_Contains in the join condition is faster blog.cleverelephant.ca/2008/10/… – plablo09 Oct 15 '15 at 23:29
3

As mapBaker said, the most important issue is having a spatial index on your geometries.

But, that being said, using prepared geometries is also way faster as stated here. So, using ST_Intersects or ST_Contains in the join condition should speed up the query.

  • so if these prepared geometries are faster, would it make any substantial differences in the results I get from using ST_Within? – Minh Mai Oct 16 '15 at 14:38
  • In your case it would make no difference at all. Since all you want to know is if a given point lies inside a polygon, the result will be the same with any of the three realtionships. You would get different results if you where relating lines or polygons – plablo09 Oct 16 '15 at 14:56
  • Thanks! I've been looking at the documentation on spatial queries. Just for a reference, do you have any idea where I can find a more intuitive explanation of the ST functions? – Minh Mai Oct 16 '15 at 16:19
  • Well, it may not be the most intuitive document, but it wouldn't hurt to read the Simple Feature Acees specification . It defines and explains all the standard (ST) functions. – plablo09 Oct 16 '15 at 16:40

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.