2

My goal is to create an application in which I have 2 "types" of data that I will be querying against. I will have points and polygons. Ultimately I will be querying to find all of the polygons that a specific point exists in. I have downloaded the worlds admin level data, once in 6 separate shapefiles (one for each admin level), and in another instance, all in 1 shapefile (combining each admin level). I am also going to have to get different shapefiles from different sources besides GADM such as OSM, or census.gov.

Is it ideal to combine all of the polygon data into one table regardless of admin level, municipality, natural feature, etc, or to create a table for each potential type (adm1, adm1, locality, etc)?

My hunch is that combining them all makes the most sense because I wouldn't have to query a variable number of tables if I am looking for all of the polygons that a point lays within.

At the same time, I worry of such a potentially large table. Again, my goal is to load massive amounts of data into this table.

Has anyone done something similar to this, and can maybe shed some light on the problem?

4

If your goal is query speed, your best bet is for the polygons to be in

  • one table
  • indexed
  • decompose/simple polygon types (not multipolygon).

I've bench marked this myself not to long ago, massive difference.

I'm unclear of this other "type" of data. I assume you're querying with the point, and not storing it in the table?

You can run a quick test with tz_world which distributes both forms complex polygon and simple polygon. Distributing/sharding data doesn't really make things faster unless you can not store the entire index in ram. The primary advantage of sharding and distributing data is to ease the backup load, table level locking, and index update speeds.

  • I am actually storing each of the points into one "places" table. Is this not ideal? I would like to say give me all the points in a polygon, but also say give me all of the polygons that contain x point. That is why I believe that I need to store them. Also I am currently storing them as multipolygons. What do you mean by decompose/simple polygon types? – tylerism Nov 22 '16 at 20:29
  • 2
    For speedy point-in-polygons queries I would make a special query table that contains all the polygons split to small geometries with ST_Subdivide postgis.net/docs/ST_Subdivide.html. – user30184 Nov 22 '16 at 21:43
  • I mean indexes on MULTIPOLYGONs are slow. So you can ST_Dump them into POLYGONs which when indexed are much faster. – Evan Carroll Nov 22 '16 at 21:46
  • And, as @user30184 said, if that's still not fast enough you can take it one step further and split polygons themselves with ST_Subdivide. – Evan Carroll Nov 22 '16 at 21:47

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.