1

I am going to be using PostGIS 2 to create a lookup service. Given a point (lat/lon), I need to either find if a product can be distributed there, or b) a list of products or distributors.

I am assuming I'd create a table with some distributor-related columns, some product-related columns, and a geographical column for stating a geographical distribution area for that distributor-product pair.

There will be hundreds of distributors and thousands of products.

  • What is the most peformant way to accomplish this? Should I use a geography datatype? Would geohashing be a better approach?
  • What would the queries look like?
  • If you have implemented something similar, what gotchas have you experienced?
3
  • 1
    Are distributors and products fairly tightly linked? How complex are the distribution area descriptions? What format do you have the data in now?
    – BradHards
    Nov 27, 2014 at 0:39
  • Not sure till I get a body of data, but a distributor doesn't necessarily offer all its products in only one area. The areas can get complex, including disparate regions. One may be national, another statewide, and a smaller can hit three different cities. Right now we have the data unsatisfactorily by zip codes.
    – alphadogg
    Nov 27, 2014 at 1:27
  • I have attempted to answer your questions -- quite a lot to cover :D. Nov 27, 2014 at 10:14

1 Answer 1

1

In general, there is more support for the geometry than the geography data type, see function comparison matrix. For your type of query, classic point in polygon, I think you can use the geometry datatype, setting the spatial reference ID (SRID) to 4326 (lat/lon). See, this thread on the difference between geography and geometry for more info.

The gist index applied to geometry/geography datatypes creates an extended R-tree, which is more efficient for point in polygon type searches than geohashing (which is essentially just a trick to squash 2-dimension into one, so it can be indexed using B-trees). R-trees work by making more or less equally balanced trees based on bounding boxes and generalize fine for areas of higher density (the boxes will just be smaller), so searching first works on minimum bounding rectangles to find potential candidates, before more expensive point in polygon calculations are performed.

So, presumably for normalization purposes, and to support many to many product/supplier relationships, you would want to have a products table, a productID, supplierID table and a supplier table, with supplierID and the geometry/geography or that supplier's area. The supplier table would look something like,

CREATE TABLE suppliers (id serial primary key, geom geometry (geometry, 4326));

Note the contraint on geom column, setting the type to geometry and the SRID to 4326. If you know you are only going to have Polygons or Multipolygons, you could further restrict this, to prevent Points or Linestrings being inserted, while geometry will take any geometric type.

Then you want to add the spatial index,

CREATE INDEX ix_spatial_suppliers_geom ON suppliers USING gist (geom);

Obviously, you can call the index what you want -- I tend to use ix_spatial_table_column.

To find if a product can be distributed in an area, you would make use of the ST_Contains function, which test if one geometry (your point) is inside another (your supplier area), eg,

SELECT productID, sp.supplierID 
FROM
   products_suppliers sp 
INNER JOIN 
   suppliers supl on sp.supplierID = supl.supplierID
WHERE ST_Contains(supl.geom, ST_SetSrid(ST_MakePoint(lon, lat), 4326));

which will return a list of product and supplier, based on a supplier's delivery region. To find if a specific product can be distributed, you would add an and clause to the where. If there were multiple suppliers for a given product, based on a user's location, you could make use the ST_Distance function, or for more accuracy ST_Distance_Spheroid between the user's location and the supplier's location in an ORDER BY, but this is going beyond what you originally asked.

As for gotchas, I can't think of any. Postgres/GIS queries of this type scale to the hundreds of millions of rows, and the supplier/product lists are presumably reasonably static, ie, this will be mostly read.

2
  • Thank you. I have been reading the PostGIS book from Manning, and basically came up generally with what you have said above, so thank you for confirming my initial direction with more details. The only difference is that if product areas change as a function of product vs supplier, then I will drop the geom column into product_supplier.
    – alphadogg
    Nov 27, 2014 at 17:02
  • 1
    You are very welcome. Yeah, sure, I figured from your SO profile that you would know how to set up your own table structure, to fit exact requirements :D. The Manning book is outstanding, the author , Regina Obe, can be found on irc #postgis. She is very good value. Nov 27, 2014 at 17:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.