Postgresql – Adding coordinates to table (forcing 1:1 association without unique identifiers on either side)

postgresqlspatial

I have a table (around 60-80 million rows) in a postgresql database, that identifies census tracts that the observation belongs to (identifies an area of the US). I want to place a random point within that polygon, and associate each observation (row) with a point.

I have written code that will create the random points, using arcGIS. (I wrote a script that will generate ~ 1kk points in <1 min) What I cannot figure out is how to tie the coordinates back to the original data.

If I pipe the coordinates back into the sql database, then i can create a 3 column table of tract ID's, X and Y coordinates. but if i join this table to the original data, then it'd essentially create a many to many join, which is not what i want. I have 1:1 data, but I don't have unique identifiers on either side.

How would i force a 1:1 association?

Best Answer

PostgreSQL uses PostGIS for spatial stuff. You're in good hands, out of all the RDBMs GIS extensions PostGIS is the most powerful.

Use the Loader_Generate_Script. It'll return a table with a geometry column.

Then you just have to run

ALTER TABLE tiger.census_table ADD random_geom geometry(4326, POINT);
UPDATE tiger.census_table
  SET random_geom = ST_GeneratePoints(census_table.geom,1);

That's the easy way. It's a 5 min job.

You usually don't want a random point though. You usually want the centroid to be useful.

Related Solutions

Postgresql – Trying to map postal codes to their respective cities, but running into some issues

Your question is not very clear.

You say you are trying to create a database of cities and zip codes, but then you say you are using Geonames, which is a database of cities and zip codes. Why are you trying to re-create it?

Places are not hierarchical. You need to think about the institution responsible for the place's boundaries. State boundaries are determined by the US Constitution. City/county boundaries are determined by city and/or state governments. Zip boundaries are determined by USPS.

Some towns contain zips. Some zips contain towns. Some zips cross state boundaries.

A mailing address needs a zip AND some other info. Usually it is a city OR a "surveyed area" such as a street address (which in turn may belong to a city).

A university also likely has more than one address ;)

I would use a little table inheritance and give country, state/prov, city and surveyed area all the same supertype, that way you can point a single foreign key at any of them.

Filling Missing Rows in PostgreSQL Database from Backup

You can restore all affected tables into a different schema, there are a variety of ways in which you can do that (several good ideas in https://stackoverflow.com/questions/4191653/i-want-to-restore-the-database-with-a-different-schema).

Once the table is in a separate schema, you can compare them by primary key, and insert the missing ones, like so for each affected table:

INSERT INTO production.table
SELECT b.*
FROM backup.table b
LEFT JOIN production.table p ON (p.pkey = b.pkey)
WHERE p.pkey IS NULL;

You will just need to find out the list of affected tables, restore their backup in a separate schema, determine what are their primary keys, create the script with the inserts, and run it.

Best Answer

Related Solutions

Postgresql – Trying to map postal codes to their respective cities, but running into some issues

Filling Missing Rows in PostgreSQL Database from Backup

Related Question