Populate National Utilities #215

rajadain · 2022-11-21T23:36:51Z

Overview

The clients gave us a list of utilities to import. This is a national list, contains ~45K+ entries. This geojson file is here: https://drive.google.com/file/d/1iZT0ilvkVafIjMMRIasXEr0zeRH6ZKLl/view?usp=share_link.

Since our data model is different, I used jq to massage the geojson into something that matches our structure:

jq '{
    type,
    name,
    "features": [(
        .features[]
        | select(.geometry != null)
        | select(.properties.state_code | inside("AK","AL","AR","AZ","CA","CO","CT","DC","DE","FL","GA","HI","IA","ID","IL","IN","KS","KY","LA","MA","MD","ME","MI","MN","MO","MS","MT","NC","ND","NE","NH","NJ","NM","NV","NY","OH","OK","OR","PA","PR","RI","SC","SD","TN","TX","UT","VA","VT","WA","WI","WV","WY"))
        | {
            type,
            geometry,
            "properties": {
                "pwsid": .properties.pwsid,
                "name": .properties.pws_name,
                "state_id": .properties.state_code,
                "address_line_1": "",
                "address_line_2": "",
                "address_city": (.properties.city_served // ""),
                "address_zip_code": ""
            }
        }
    )]
}' scratch/pws_centroids_usa.geojson > scratch/utilities.geojson

The above also filters out utilities that don't have a location, and those that do not belong to the 50 official states.

Then I imported that geojson into my local database:

ogr2ogr -f 'PostgreSQL' PG:'host=localhost dbname=iow-boundary-tool user=iow-boundary-tool password=iow-boundary-tool' scratch/utilities.geojson -nln api_utility -append -geomfield location

I then edited the docker-compose.override.yml file to allow access into the database container:

diff --git a/docker-compose.override.yml b/docker-compose.override.yml
index c2610ab..6f8af0c 100644
--- a/docker-compose.override.yml
+++ b/docker-compose.override.yml
@@ -1,5 +1,10 @@
 version: "2.4"
 services:
+  database:
+    volumes:
+      - ./src/django/data:/usr/local/src
+    ports:
+      - 5432:5432
   app:
     ports:
       - 4545:4545

Then I went inside and pg_dumped the table out:

docker compose exec database /bin/bash
cd /usr/local/src
pg_dump -U iow-boundary-tool -h localhost --table=api_utility --data-only > api_utility_20221121.sql

I manually removed the first two rows containing development / test utilities. Then I uploaded the file to a new S3 bucket:

aws --profile=iow-boundary-tool s3 ls iow-boundary-tool-initialization-us-east-1/
2022-11-21 17:36:06    4565611 api_utility_20221121.sql

I then scpd the file to staging and production and ran it there:

scp src/django/data/api_utility_20221121.sql iow-staging:
ssh iow-staging
sudo yum install postgresql
psql -h database.service.iow.internal -U iow iow -1 -f api_utility_20221121.sql

Also makes it so that the map initializes at the selected utility's location, rather than always in North Carolina.

Closes #196

Demo

2022-11-21.18.49.42.mp4

Testing Instructions

Check out this branch and run server
If you have access to the AWS account, run the following:
```
./scripts/fetch-data --utilities
```
- Ensure this completes successfully
Go to http://localhost:8181/admin/ and login as a1@azavea.com / password
Go to http://localhost:8181/admin/api/utility/
- Ensure you see a lot of utilities

Checklist

fixup! commits have been squashed
CHANGELOG.md updated with summary of features or fixes, following Keep a Changelog guidelines
README.md updated if necessary to reflect the changes
CI passes after rebase

rajadain · 2022-11-27T23:07:10Z

Rebased on latest develop

caseycesari · 2022-11-28T15:28:32Z

Taking a look

caseycesari

Works well!

scripts/fetch-data

To be used like: ./scripts/fetch-data --utilities The utility IDs start with 3, so should work with the two test utilities in development, as well as in other environmnents where those test utilities are absent.

Since now there will be ~45K utilities in the system, we improve the utility admin to have search and filter capabilities to be a little more manageable.

Now that we have live utilities, instead of always initializing the map on the same North Carolina spot, we initialize it where the selected utility's centroid is, falling back to the old default if necessary.

This was not used in the UI.

rajadain · 2022-11-28T16:52:04Z

Thanks for reviewing! Rebased above latest develop. Fixed the postgres password prompt in 3a9a3fb. Will merge once green.

rajadain requested a review from caseycesari November 23, 2022 18:25

rajadain assigned caseycesari Nov 23, 2022

rajadain force-pushed the tt/import-utilities branch from a38dd15 to 38c636e Compare November 27, 2022 23:06

caseycesari approved these changes Nov 28, 2022

View reviewed changes

scripts/fetch-data Outdated Show resolved Hide resolved

caseycesari assigned rajadain and unassigned caseycesari Nov 28, 2022

rajadain added 5 commits November 28, 2022 11:48

Add fetch utilities script

3a9a3fb

To be used like: ./scripts/fetch-data --utilities The utility IDs start with 3, so should work with the two test utilities in development, as well as in other environmnents where those test utilities are absent.

Enhance Utility Admin

70a68cf

Since now there will be ~45K utilities in the system, we improve the utility admin to have search and filter capabilities to be a little more manageable.

Initialize user map on utility location

b47f66a

Now that we have live utilities, instead of always initializing the map on the same North Carolina spot, we initialize it where the selected utility's centroid is, falling back to the old default if necessary.

Remove extraneous layer

6545aac

This was not used in the UI.

Update CHANGELOG

7e88f58

rajadain force-pushed the tt/import-utilities branch from 38c636e to 7e88f58 Compare November 28, 2022 16:49

rajadain merged commit 3b979d4 into develop Nov 28, 2022

rajadain deleted the tt/import-utilities branch November 28, 2022 17:06

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Populate National Utilities #215

Populate National Utilities #215

rajadain commented Nov 21, 2022 •

edited

Loading

rajadain commented Nov 27, 2022

caseycesari commented Nov 28, 2022

caseycesari left a comment

rajadain commented Nov 28, 2022

Populate National Utilities #215

Populate National Utilities #215

Conversation

rajadain commented Nov 21, 2022 • edited Loading

Overview

Demo

Testing Instructions

Checklist

rajadain commented Nov 27, 2022

caseycesari commented Nov 28, 2022

caseycesari left a comment

Choose a reason for hiding this comment

rajadain commented Nov 28, 2022

rajadain commented Nov 21, 2022 •

edited

Loading