postgresql

Issues:

• lots of TIGER data, schemas, view installed
• users: adminw, reader in transfer sql, but not opal
• need to create users: pguser, reader

setup

setup .bashrc

    PGUSER=locations@opal4.opalstack.com
    PGPASSWORD=<pw>

their admin tool: Adminer

like pgAdmin, but (supposedly) better

hmm..., looks like the @opal4... part isn't needed:

> psql -U locations locations
psql (11.8)
Type "help" for help.

Had to request sysadmin to enable PostGIS, then:

    locations=> create table aaa ( label text, geom Geometry );
CREATE TABLE

    locations=> \d aaa
                 Table "public.aaa"
 Column |   Type   | Collation | Nullable | Default
--------+----------+-----------+----------+---------
 label  | text     |           |          |
 geom   | geometry |           |          |


    locations=> insert into aaa values ('aaa', ST_SetSRID(ST_Point(-77.0596, 38.9383),4326));
INSERT 0 1

locations=> select label, st_astext(geom) from aaa;
 label |        st_astext
-------+-------------------------
 aaa   | POINT(-77.0596 38.9383)
(1 row)

Yes, looks like PostGIS is installed.

status of ilc tables

Database: location_data

users

admin user seems to be same as database

TODO: Managing PostgreSQL users and roles

transfer from ilc

• on ilc:

    $ pg_dump -U adminw  -F p -t mylocations location_data > mylocs.sql
    $ pg_dump -U adminw  -F p -t cheesemag location_data > chmag.sql
    $ pg_dump -U adminw  -F p -t dishing_nj location_data > nj.sql
    $ pg_dump -U adminw  -F p -t farmers location_data > fmr.sql
    $ pg_dump -U adminw  -F p -t roadside location_data > road.sql

and then:

    $ tar zcvf ilc_tbls.tgz *.sql
    $ scp ilc_tbls.tgz  wendell@carrotseverywhere.com:

• on c'evry:

    > psql -d locations -f chmag.sql

SET
 set_config
------------
(1 row)

SET
CREATE TABLE
psql:chmag.sql:37: ERROR:  role "adminw" does not exist
COPY 91
REVOKE
psql:chmag.sql:144: ERROR:  role "adminw" does not exist
psql:chmag.sql:145: ERROR:  role "reader" does not exist

Opal Issue: we CANNOT create roles or users!!!
SO, just install everything and hope for the best...

    psql -d locs -f fmr.sql
    psql -d locs -f chmag.sql
    psql -d locs -f mylocs.sql
    psql -d locs -f road.sql
    psql -d locs -f nj.sql

Done.

sample query

psql locations psql (11.8) Type "help" for help.

locations=> select cname, address from cheesemag where address like '%DC%'; cname | address -----------------------------+-------------------------------------------------- Bowers Fancy Dairy Products | 225 Seventh St. S.E., Washington, DC 20003 Dean & Deluca | 3276 M St. N.W., Washington, DC 20007 Via Umbria | 1525 Wisconsin Ave NW, Washington, DC 20007 Wagshal's | 4855 Massachusetts Ave. NW, Washington, DC 20016 Wagshal's | 3201 New Mexico Ave. NW, Washington, DC 20016 (5 rows)

now without TIGER data

having just installed, with no tables:

> psql locs
psql (11.8)
Type "help" for help.

locs=> \d
                 List of relations
  Schema  |       Name        |   Type   |  Owner
----------+-------------------+----------+----------
 public   | geography_columns | view     | postgres
 public   | geometry_columns  | view     | postgres
 public   | raster_columns    | view     | postgres
 public   | raster_overviews  | view     | postgres
 public   | spatial_ref_sys   | table    | postgres
 topology | layer             | table    | postgres
 topology | topology          | table    | postgres
 topology | topology_id_seq   | sequence | postgres
(8 rows)

locs=> \dt
               List of relations
  Schema  |      Name       | Type  |  Owner
----------+-----------------+-------+----------
 public   | spatial_ref_sys | table | postgres
 topology | layer           | table | postgres
 topology | topology        | table | postgres
(3 rows)