[postgis-users] Partitionning using geometry

[Rémi Cura]

Hey Arnaud,
thanks for this good idea.

I hadn't thought of that!
I really like the concept of reading more or less data to get more or less
precision, I used it for point cloud Level of Details.

Indeed it could be used in partiton check.

We would still have the limit that the geohash used to filter stuff would
need to be hard written in the query (like using a bbox or other geometry
type)

Cheers,
Rémi-C


2015-04-03 11:50 GMT+02:00 Arnaud Lesauvage <arnaud.listes at codata.eu>:

> Hi Rémi,
> have you taken a look at ST_GeoHash ?
> It might be a good candidate for your constraint exclusion check.
> There are restrictions though. Coordinates must be in lat/lon for instance.
> http://postgis.net/docs/ST_GeoHash.html
>
> Arnaud
>
> Le 3/04/2015 10:49, Rémi Cura a écrit :
>
>> Now postgres offers partitioning, that is that you build a hierarchy of
>> tables.
>>
>> In this case, you would have one empty father table, and all the
>> thousand table would be declared as child of the father table.
>> Now when you write
>> select * from father;
>> you query in fact all the child tables.
>>
>> This is all good and working, but it will be inefficient, because each
>> time you look for geometry within a rectangle, you would have to read
>> all tables (using their index).
>> Of course having thousands of indexes in memory is not possible, so it
>> would be very slow.
>>
>> Postgres offers a solution for that, which is to declare constraints
>> (check) on table.
>>
>> So you would say, table child_1 is entirely contained in a rectangle R1,
>> table child_2 entirely contained in a rectangle R2, etc.
>>
>> That way, when you query the father table and asking all the geometry
>> inside a rectangle R0,
>> the planner will first check in which tables i Ri intersects R0,
>> then it will only consider those tables, instead of considering all the
>> tables.
>>
>> This is the theory. In practice the planner was not using those
>> constraints (check).
>>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150403/d4efa329/attachment.html>