SME support to populate a spatial database

[dneufeldcu]

Summary

As a	GIS developer and web map viewer supporter
I want to be able to	display spatial data associated with granule level data stored in files
So that I can	link in my GIS web mapping stack and allow fine grained spatial data search capabilities

Tasks

• [x] Using our python client listen for incoming CSB granules and populate points table
• [x] Points into a spatial database
• [x] Use sql insert or geopanda connection potentially

Reference

• There's some existing code here https://github.com/cedardevs/csbCrawler2Cloud that may be useful
• Our python-client: https://github.com/cedardevs/onestop-clients/tree/master/python-client
• Ex: https://github.com/cedardevs/onestop-clients/tree/1252-kafka-SQS/examples
• Ex: https://github.com/cedardevs/onestop-clients/blob/master/examples/smeFunc.py
• You are going to need to get IM going locally to work on this btw since you'll need messages to consume and the easiest way to do that is to stand up IM and put data into it
• DB to be determined. Perhaps stand up a local one temporarily? DB python packages https://pypi.org/project/postgis/
• Check metadata-recorder or Jesse for python?

[erinreeves]

https://github.com/cedardevs/onestop-clients/tree/master/python-client#how-to-publish-a-new-version-of-this-client

[JeffreyHuynh1]

Was getting a lot of errors trying to install psycopg2, fixed it with this command in pycharm terminal -env LDFLAGS="-I/usr/local/opt/openssl/include -L/usr/local/opt/openssl/lib" pip install psycopg2

[JeffreyHuynh1]

You can create postgres tables that allow for postgis in python code, but using the pgadmin UI for the time being

[JeffreyHuynh1]

In order to use postgis extension I need to be a superuser. Getting the following error 'ERROR: permission denied to create extension "postgis" HINT: Must be superuser to create this extension.'

Tried using 'ALTER ROLE onestop SUPERUSER; ' but got the following error 'ERROR: must be superuser to alter superusers'

Found some docs suggesting to use : su postgres psql alter role user_name superuser; #then create the extension as the user in a different screen alter role user_name nosuperuser;

Unsure about how to incorporate this with docker though

SOLUTION: Created credentials via helm to log into the default superuser role which is 'postgress'. Was able to use the postgis extension on tables after this.

[JeffreyHuynh1]

Geometry points in postgis will be displayed as hex. To change the hex into a tuple for a viewable format, you can use the query command

• SELECT *, ST_AsText(geom) FROM table_name;