I work with income data linked to 19th century U.S. town names, and I have to join the income data with town points (year 2014) shapefiles. Many of the towns changed name for several times, I manually search for the current location of historical town names, and want to record such info in PostGIS.

I thought about using synonyms for a historical town names and its current town name in PostgreSQL, but it seems that PostgreSQL does not have CREATE SYNONYM statement....

Another way to do this might be changing the historical town names into its current town names in my income table, and then join with the 2014 town points shapefile, but that is my last resort since I want to keep the original town names.

Is there any other way to work around this issue?

  • Couldn't you create a new column in the historical town table which contains the modern towns name, id or other unique value. This would be a foreign key you could use for your join. – RoperMaps Jun 24 '17 at 13:01

I would say create some TOWNID in your TOWNS points table.

Then create a 'townname' table that has columns for TOWNID, YEAR (maybe formatted as DATETIME if you wanted to do any time-slider type of visualization - or just integer to store year), and anything else you might want, and begin populating it with the town names over the years.

That data can be used in a join with your TOWNS table in any other queries or QGIS visualizations.

  • Thanks for your reply! It's been a while, but could you explain a bit how the TOWNID helps identify the different names of the same town location? – iuiu Sep 16 '17 at 12:27
  • I'm suggesting using one TOWNS table to store the individual towns (point, townid, name, etc.). Then in a HISTORICAL_TOWNS table, each town in the TOWNS table shares the same TOWNID as the historic name assigned to each point in time. Then when you want to show the TOWNS by their historic name in say 1835, the record in the HISTORIC_TOWNS table, which is joined to the TOWNS table by TOWNID, can display the historic name at the location in TOWNS... does that make sense at all...? – DPSSpatial Sep 18 '17 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.