Adding LINESTRING support to poi_poly

[ValorNaram]

See https://github.com/babykarte/babykarte/issues/45 where a playground equipment feature has been mapped as LINESTRING and not shown in database or in playground statistics because of being not imported.

[giggls]

Do you have examples? Up till now I was not aware that there are such things as line shaped points of interest.

[ValorNaram]

https://www.openstreetmap.org/way/566960842

[giggls]

You also added historic, man_made and emergency. What about those?

Beside this, did you check if this actually works with a planet extract?

[ValorNaram]

historic, man_made and emergency

Nothing important. Just imagined that such objects can be also LINESTRING. But we can remove this. I just wanted to help you with that by adding also what your maps use.

[ValorNaram]

Beside this, did you check if this actually works with a planet extract?

No because I do not have any syntax errors in the file.

[giggls]

I am unsure about the spatial join if poi_poly contains linestrings. Probably we should use a sepearte table.

[ValorNaram]

Postgis can handle such cases, no? The functions working with geometry types can handle such cases, no? I have ones used a function determining, if a LINESTRING coordinate is inside a POLYGON one.

[giggls]

I have no Idea what happens, but st_contains will certainly fail in some way with a linestring instead of a polygon. I would definitely opt for a separate linestring table. I also think, that we might need to build some test-data to make sure the import actually does what we expect it to do.

[ValorNaram]

I have no Idea what happens, but st_contains will certainly fail in some way with a linestring instead of a polygon. I would definitely opt for a separate linestring table.

I will create a test database locally with linestring data.

[giggls]

Use a separate table osm_poi_line and add it to the osm_poi_all VIEW.

At least this is the way I would try to do it :)

[ValorNaram]

Do you mean locally or on the server?

[giggls]

Huh? Of corse this should have been a hint on how I would do it and not how to deploy it (yet). This may then go into the production database after it has been proven to work.

[ValorNaram]

The command

imposm import -config $2 \
-read $1 \
-write -diff -dbschema-import="public"

fails with imposm: error: option -c: invalid integer value: 'config.json'. It is an error on my site or is the doimport.sh script of this repository not working property?

[giggls]

Huh? Are you shure, that you are actually using imposm3?

[giggls]

Sorry, I can not reproduce this error. I just tested importing a Geofabrik data extract of my local area and this just worked fine following the instruction in INSTALL.md

[ValorNaram]

Huh? Are you shure, that you are actually using imposm3?

This was the problem. Thx

[giggls]

I am currently fiddling with this. I will push a corresponding branch in a few minutes.

[giggls]

OK, I just added a new branch called linestring. Please check if it works.

[ValorNaram]

Will check it

[ValorNaram]

postgres@soren:linestring/osmpoidb$ psql -f gen_indexes.sql gis
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
postgres@soren:linestring/osmpoidb$ psql -f country_osm_grid.sql gis
SET
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
COPY 23217
CREATE INDEX
postgres@soren:linestring/osmpoidb$ psql -f gen_mview_poi_campsites.sql gis
CREATE FUNCTION
CREATE VIEW
SELECT 504
CREATE INDEX
CREATE INDEX
psql:gen_mview_poi_campsites.sql:179: ERROR:  materialized view "osm_poi_campsites" does not exist
ALTER MATERIALIZED VIEW
ALTER INDEX
ALTER INDEX
GRANT
postgres@soren:linestring/osmpoidb$ psql -f gen_mview_poi_playgrounds.sql gis
CREATE VIEW
SELECT 4387
CREATE INDEX
CREATE INDEX
psql:gen_mview_poi_playgrounds.sql:73: ERROR:  materialized view "osm_poi_playgrounds" does not exist
ALTER MATERIALIZED VIEW
ALTER INDEX
ALTER INDEX
GRANT
postgres@soren:linestring/osmpoidb$ psql -f gen_mview_poi_playgrounds.sql gis
CREATE VIEW
SELECT 4387
CREATE INDEX
CREATE INDEX
DROP MATERIALIZED VIEW
ALTER MATERIALIZED VIEW
ALTER INDEX
ALTER INDEX
GRANT

Works like a charm. Just had authorisation problems because my authorisation setup differs.

[giggls]

Would be good to also check the output of a test-query on osm_poi_playgrounds which actually contains a line shaped POI.

[ValorNaram]

Wait

[ValorNaram]

need to switch my local Babykarte into debug mode

[ValorNaram]

The database table osm_poi_playgrounds does not contain them but the table osm_poi_line does.

[giggls]

NACK osm_poi_playgrounds does contains them (it in form of equipment list).

An example from my area:

sven=# select equipment from osm_poi_playgrounds where osm_id=40306226;
 equipment 
-----------
 {zipwire}
(1 Zeile)

Objects are: https://www.openstreetmap.org/way/40306226 (the playground) and https://www.openstreetmap.org/way/110072208 (the line shaped object, singular in this case)

[ValorNaram]

NACK osm_poi_playgrounds does contains them (it in form of equipment list).

Ooof right. Forgot about that. Need to add the osm_poi_line to my query in the Babykarte backend.

[giggls]

osm_poi_all contains linestrings, polygons and points now.

[ValorNaram]

But it is a view. A view is rebuild everytime when someone uses it. So it puts a burden on the server.

[giggls]

This view is cheap its just union of 3 Tables.

BTW, am I right in my suspicion, that osm_poi_playgrounds is not currently used in Babykarte and we do not even need all these spatial joins which will group features to playground objects?

[ValorNaram]

osm_poi_playgrounds is not currently used in Babykarte and we do not even need all these spatial joins which will group features to playground objects?

It is used when someone searches for playgrounds in the map view.

[giggls]

This spatial join stuff is what these MATERIALIZED VIEWS are all about. Ordinary VIEWS would be way too slow in this case.

[ValorNaram]

We can integrate that into the database, right?

[giggls]

Yes, but this would need a re-import of the Database then. Disk-space looks fine. So I will stop replication and do this.