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?


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.

| improve this answer | |
  • The database I have is postgis enabled. Like i said, i have around 80 million rows that identify the census tract (by ID), but they don't have the tract geometry. I must stress I'm not an SQL professional, I just know how to put together a query. Centroid is not really useful in this case, as i'll be plotting the dots on a map. (trust me, it makes sense in my context.) – Faydey Dec 22 '17 at 23:54

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.