0

Let's assume you have a huge PostGis spatial table (using Postgresql 9.2, postgis 1.5) :

BIGTABLE ( p Point, someData varchar )

which contains millions of records. Each record has been created with a Point and each point has an associated varchar data (let's call it the useful information).

Now, let's say there is another spatial table from which you also can get a Point field :

SOMESEARCHPOINTS ( ... , p Point , ...)

For a given set of points from the SOMESEARCHPOINTS table (which are retrieved from a subquery on this table), I want to find the corresponding "someData" varchar using a JOIN.

SELECT * FROM SOMESEARCHPOINTS sp
LEFT JOIN BIGTABLE b ON ( XXXXXXXXXXX ) 

The XXXXXXXXXXX being the join condition.

The question is : without modifying the current schema (meaning without changing the geometry type), is it possible to have an efficient XXXXXXXXXXX join condtion ? If not, what is the proper way to do that ?

  • 1
    This is what spatial indexes are designed to do, efficiently -- essentially build a sequence of successively smaller boxes to efficiently search in 2d. There is no substitute for testing, but 10 million is well within Postgres/Postgis's capabilities and if you start buffering things, you are adding complexity for no extra performance gain. – John Powell Oct 25 '14 at 8:27
  • Thanks. I guess the performance issues are then more related to the query itself then (basically, the join is done on a too great number of rows). – Vole Rig Oct 25 '14 at 8:39
  • 1
    @Vince. Really. I have tables of 500 million geometries that return everything inside a search polygon in milliseconds, just using a Gist index. – John Powell Oct 25 '14 at 13:04
  • 3
    Creating lots of buffers is a bad idea at query time. I'm still not entirely clear what you are trying to do: can't you just buffer a single search polygon and find all points inside it -- which will be efficient as it will use the index and only involve buffering one object? – John Powell Oct 25 '14 at 13:06
  • 1
    Using functions like st_contains, st_buffer and st_distance for searching is nearly always the wrong way to go. You should investigate and use the st_dwithin function. There are many questions or answers involving st_dwithin on this site. – Martin F Oct 26 '14 at 21:57
1

If the points that you want to join is really identical (you wrote one table is a subset of the other) you should be able to just join on intersecting bboxes.

If you have a working spatial index that will be very fast. Just use the && operator between the points.

If the points is not identical you can expand one of the bboxes. That is what st_dwithin does before rechecking by calculating the exact distances. But if approx distance is enough to find the right point comparing bboxes is enough.

  • The points are not identical (well, they might be, but it would just be a coincidence) If the points is not identical you can expand one of the bboxes. How can a point have a bounding box ? I have tried with st_dwithin, and it is very slow. – Vole Rig Oct 28 '14 at 17:43
  • Well, It can. It could be just that the box have all corners in the point. In PostGIS bboxes is represented with Int32 coordinates and the point with Int64 coordinates. So the box actually has a small area. it doesn't matter if the index gives a few false positive intersections since the result is rechecked. – Nicklas Avén Oct 28 '14 at 18:55
  • about "very slow" I have to ask if you really have a functional spatial index. do explain tell you that you have an index scan? and is "very slow" compared to something else or just takes to long to run your query? the slow part of st_dwithin is the recheck where all results from the index scan is checked by real distance calculations. if you just expand bboxes and use the && operator it will be very fast. Look at the sql-definition of st_dwithin how it is done. there is a special trick to keep the index working by expanding one of the two comparing points and then the other. – Nicklas Avén Oct 28 '14 at 19:02
  • thanks for the explanation about boxes. Well, it is slow compared to just using ST_Contains between the points (slow meaning I have to interrupt the query).... but using st_contains takes about 1.5 seconds for searching 500 points among 3M points....I thought it could have been faster....but after reading your explanation, there might be no other way.... – Vole Rig Oct 28 '14 at 19:26
  • Using && is as fast as using st_contains...and Explain analyze reports that it takes 1108.000 ms to do the index scan on the BIGTABLE. (83% of the total query time). – Vole Rig Oct 28 '14 at 19:46

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.