NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum

I am building an application for a customer in which they will type the name of a place (i.e. the name column on planet_osm_polygon of a osm2pgsql imported database) and get a boundary. I was successful in setting up the system, and I've been able to optimize the planet_osm_polygon table for trigram searching (with pg_trgm). The system now works -- awesome!

However, I'd like to "join" the tags column from the planet_osm_rels table by the osm_id provided in results from planet_osm_polygon. I understand that osm2pgsql does not provide any foreign key constraints. With that said, I'd like to know:

If it possible for me to add this foreign key relationship myself?

Any recommendations for re-approaching the problem are welcome.

asked 03 Feb '18, 15:09

ethanhinson's gravatar image

ethanhinson
21112
accept rate: 0%


There is no reasonable way to add foreign keys to this schema.

The way to accomplish this is to make sure you use the --hstore-all option. This will create a new column on the polygon table (planet_osm_polygon in my case) which contains all the tags associated with that particular polygon.

permanent link

answered 04 Feb '18, 17:40

ethanhinson's gravatar image

ethanhinson
21112
accept rate: 0%

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×263
×165
×134
×128

question asked: 03 Feb '18, 15:09

question was seen: 1,805 times

last updated: 04 Feb '18, 17:40

NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum