From the manual:
osm2pgsql relies much on its node cache during import. If the nodes do
not fit into the cache it needs to do database lookups which slow down
the process. Use enough cache so all nodes are cached. -C 12000 seems
to do the job, even if that means you have to configure more swap
space.
Try to use -C 'somethingbig'
See the wiki
If you want to try and build this executable yourself, just clone the github code and check the README file, it states somewhere:
On most Unix-like systems the program can be compiled by
running './autogen.sh && ./configure && make'.
So if you have all the libs/reqs fulfilled you'll be building an executable that works.
To search for points around that location in London, you'd want a query like this
SELECT *
FROM planet_osm_polygon
WHERE ST_Intersects(way, ST_Buffer(ST_Transform(ST_SetSRID(ST_Point(-0.12,51.5),4326), 3857), 500);
This will take the point, transform it into the web mercator projection (EPSG 3857) used by default in osm2pgsql, buffer it by 500 mercator meters, and find points that lie within that polygon.
A more accurate way to do this would be
SELECT *
FROM planet_osm_polygon
WHERE ST_Intersects(way,
ST_Transform(ST_Buffer(
ST_SetSRID(ST_Point(-0.12,51.5),4326)::geography,500)::geometry,
3857)
);
This converts to geography so you can expand the point by 500 true meters.
A third, and probably quickest way, is
SELECT *
FROM planet_osm_polygon
WHERE ST_DWithin(way, ST_Transform(ST_SetSRID(ST_Point(-0.12,51.5),4326), 3857), 500);
Of course in practice you'll have some other WHERE conditions to apply, and be using a more complicated real-world geometry to search against.
Some common mistakes are
- Trying to compare geometries of different projections
- Reprojecting the geometry in the
planet_osm_point
table, preventing index usage
- Mixing up latitude and longitude.
It's worth noting that PostGIS geometries are completely distinct from PostgreSQL geometric types. The appropriate page to see available functions is Chapter 8 of the PostGIS reference.
Best Answer
The difference is in the names:
planet_osm_point
contains point features from OSM, that is, nodes that have tags on them. Columns in both tables are tags, plusway
for geometry.Planet_osm_polygon
contains polygons and processed multipolygons (so you don't need to collect multipolygons yourself). So, for example, building contours would be included in_polygon
table, while shop nodes have to be collected from_point
.I don't know why it was decided to split points and polygons (and lines) into different tables. This probably allows disk space optimization, for tags allowed for those types of geometries are different (see
default.style
from osm2pgsql directory).