giswqs / postgis

Spatial Data Management with PostgreSQL and PostGIS https://gishub.org/sdm

postgis

Spatial Data Management with PostgreSQL and PostGIS https://gishub.org/sdm

Sample Datasets

• https://github.com/giswqs/data
• cities.csv

Useful Resources

• https://www.w3schools.com/sql/default.asp

SQL Tutorial

• https://www.scaler.com/topics/sql/

Change column type

ALTER TABLE cities
ALTER COLUMN population TYPE INT
USING population::integer

SQL queries for non-spatial data

The examples below use the sample dataset - cities.csv

• SELECT * FROM cities
• SELECT * FROM cities LIMIT 10
• SELECT name, country FROM cities
• SELECT DISTINCT country FROM cities
• SELECT COUNT(DISTINCT country) FROM cities
• SELECT MAX(population) FROM cities
• SELECT SUM(population) FROM cities
• SELECT AVG(population) FROM cities
• SELECT * FROM cities ORDER BY country
• SELECT * FROM cities ORDER BY country ASC, population DESC
• SELECT * FROM cities WHERE country='USA'
• SELECT * FROM cities WHERE country='USA' OR country='CAN'
• SELECT * FROM cities WHERE country='USA' AND population>1000000
• SELECT * FROM cities WHERE country LIKE 'U%'
• SELECT * FROM cities WHERE country LIKE '%A'
• SELECT * FROM cities WHERE country LIKE '_S_'
• SELECT * FROM cities WHERE country IN ('USA', 'CAN', 'CHN')
• SELECT * FROM cities WHERE population BETWEEN 1000000 AND 10000000

postgis's Issues

Unable to connect to postgres through python

Hi Dr. Qiusheng Wu
I am not able to connect to postgres through python.

I am getting following error while connecting

(psycopg2.OperationalError) could not translate host name "2712@localhost" to address: Unknown server error

(Background on this error at: https://sqlalche.me/e/14/e3q8)
Connection info needed in SQLAlchemy format, example:
postgresql://username:password@hostname/dbname
or an existing connection: dict_keys([])

Raster data in postgis

Hi Dr. Qiusheng Wu

I am working on satellite imagery. I want to save satellite imagery in postgis and upload it map. I am getting problem in making a table from Geotiff file. I want to send a query on geotiff file so that I could select the particular region of interest.

Please help to upload Geotiff file in postgres/postgis

I have made an application using Google earth engine.

https://sabudh.users.earthengine.app/view/myfarm2.

I want to make this type of application using Ipyleaflet and Postgres/postgis.