I am new to PostgreSQL and GIS.
I do have a downloaded dump of OSM data (map of Germany) available, which was imported via osm2pgsql into a PostGIS-enabled PostgreSQL database germanyPG.
The query
SELECT PostGIS_Full_Version();
returns
POSTGIS="2.3.3 r15473"
GEOS="3.6.1-CAPI-1.10.1 r4317"
PROJ="Rel. 4.9.1, 04 March 2015"
GDAL="GDAL 2.2.1, released 2017/06/23"
LIBXML="2.7.8" LIBJSON="0.12"
I also have an empty PostGIS-enabled PostgreSQL database subsetGermanyPG available which has same properties (database schema, structure, tables, columns, indexes, etc.) as germanyPG database.
Question 1:
Once I've given the coordinates of a rectangle (see Figure 1).
How can I query the germanyPG database to get all objects that lie within that given rectangle or overlap with the rectangle (especially those that are much larger than the rectangle) and enclose it (see Figure 2)?
I am not sure whether ST_Intersects in combination with ST_MakeEnvelope (minLong, minLat, maxLong, max Lat, srid) will do the job because I don't have an idea how to verify the query results...
SELECT realname
FROM domain
WHERE ST_Intersects(ST_MakeEnvelope(11.5, 49.8, 12.0, 50.1, 4326),
geom:: GEOMETRY);
Question 2:
What is the most elegant way to copy this queried results from germanyPG database into the second database subsetGermanyPG? Notice that both databases are on the same remote host system (server side) and I need to use a JAVA JTS application (client side) who 'coordinates' respectively does the copy job.
I know that I could use JAVA to query the germanyPG database and afterward store the results of the query in a JAVA datatype (maybe one or more arrays?) and finally insert these data sequentially in subsetGermanyPG database.
But what if the query is extensive (multiple GBs) and needs a long time. I think such extreme cases would result in network traffic issues, main memory leaks, and so on.
So I hope there is a more resource-saving solution out there?
Please note, that I am only allowed to use JAVA standard library inclusive JTS.