1

Using PostGIS & PostgreSQL (Amazon RDS instance), I need to create a (quite large) table with points (just x, y coordinates) Then what I need to do is very simple point-in-polygon queries. Points and polygons are in 2D space (not geography types).

I actually managed to do this with Amazon Athena with this simple query: SELECT ST_WITHIN(ST_POINT(x, y), ST_POLYGON('polygon((1 1, 11 44, 44 44, 44 11))')) FROM "my_table"

How it would be possible to have this kind of query working at PostgreSQL?

  • 1
    Please take the Tour which emphasizes the importance of asking One question per Question. There are a number of tutorials on the web which cover table creation, geometry population, spatial query, indexing, and query optimization with PostGIS/PostgreSQL. There are also numerous questions and answers here is GIS SE on these topics (try searching on your tags before asking your question, to see if it's already been asked). – Vince Apr 24 at 23:25
  • 1
    please only ask 1 question - you can create more individual questions. – Mapperz Apr 24 at 23:58
  • Edited the question and removed the extra question – celalo Apr 25 at 0:00
0

Start with setting up your RDS with the official guide for PostGIS https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html#Appendix.PostgreSQL.CommonDBATasks.PostGIS

use your role (rdsadmin/superuser)

create extension postgis;
CREATE EXTENSION
create extension fuzzystrmatch;
CREATE EXTENSION
create extension postgis_tiger_geocoder;
CREATE EXTENSION
create extension postgis_topology;
CREATE EXTENSION

Then

alter schema tiger owner to rds_superuser;
ALTER SCHEMA
alter schema tiger_data owner to rds_superuser;
ALTER SCHEMA
alter schema topology owner to rds_superuser;
ALTER SCHEMA

Then check your connection

psql --host xxxxxx.xxxxxxxx.us-xxxx-x.rds.amazonaws.com (or IP)
--port 5432
--username rds_superuser
--password xxxxxxxxxxxx
--dbname gis

In QGIS you can connect and with the right privileges you can save to that location. via SSH Tunnel https://support.cloud.engineyard.com/hc/en-us/articles/205408088-Access-Your-Database-Remotely-Through-an-SSH-Tunnel

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.