[OSM-dev] MySQL GIS extensions - some tipps

[Andreas Kalsch]

Marcus Wolschon schrieb:
> On Wed, Jul 22, 2009 at 4:25 PM, Andreas Kalsch<andreaskalsch at gmx.de> wrote:
>   
>> I don't render maps with it, so I don't know how it scales. But Mapnik
>> will connect easier to Postgres/PostGIS. I will outsource rendering for
>> my project.
>>     
>
> Note: I don`t use Mapnik. I was talking about _interactive_ rendering.
> It`s a routing-application and I`m just testing if offering MySQL as an
> additional supported local map-format works out. Other offered
> formats are OsmBin, the H2-embedded database, xml-files (for testing),
> in-memory, ....
>   
OK, I am sure this is scalable - but you should compare yourself. 
Haven't done that so far.
>   
>>> Can you decompose a POINT into lat+lon in an SQL-query?
>>> If not, how much space is wasted by having all coordinates
>>> twice?
>>>
>>>       
>> Yes.
>>     
>
> Yes - you can decomposit them or
>   
Yes - with X() and Y()
> Yes - you have to store a Point AND Lat+Lon as separate columns?
>   
No - you don't have to. But I store them separately into two tables 
because I don't want all nodes to be gisified. Using lat/lon as int will 
need 8 bytes. Using a Point will use 20 bytes. So if you don't need to 
index a point, save it as lat/lon.
>   
>> But I just put relevant features into the geo database - I save
>> nodes and ways which have relevant tags, and relations as
>> GeometryCollections and MultiPolygons. It makes no sense to put nodes
>> and ways into the GIS table, which are just parts of ways/relations and
>> do not play an own role. Result: 5 of 17.3 GB (for Europe) is for GIS
>> data. I use the GIS table for analysis and re-computing. It pays off.
>>     
>
> How much would that be without filtering? Since I have no clue what
> are "relevant tags and relations" for you.
>   
Relevant features are features which represent a GeoObject. Nodes which 
are just part of ways and ways which are just part of multipolygons are 
not relevant in this context.

How many features are relevant as their own:

- nodes 4 %
- ways 99 %
- relations 81 %