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

Question

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?

Answer 1

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.