Extract geometries from bounding box (within and overlapping) and copy results to new empty database

Question

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)?

Figure 1

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.

Answer 1

This can be achieved using a simple GeoTools program, simply open the two data base tables as datastores and copy the features from one to the other. In fact this will work for any supported GeoTools' datastore.

import java.io.IOException;
import java.net.MalformedURLException;
import java.util.HashMap;
import java.util.Map;

import org.geotools.data.DataStore;
import org.geotools.data.DataStoreFinder;
import org.geotools.data.FeatureSource;
import org.geotools.data.postgis.PostgisNGDataStoreFactory;
import org.geotools.data.simple.SimpleFeatureStore;
import org.geotools.factory.CommonFactoryFinder;
import org.geotools.geometry.jts.ReferencedEnvelope;
import org.opengis.feature.simple.SimpleFeature;
import org.opengis.feature.simple.SimpleFeatureType;
import org.opengis.filter.Filter;
import org.opengis.filter.FilterFactory2;
import org.opengis.referencing.crs.CoordinateReferenceSystem;

public class CopyPostgis {

  private static final FilterFactory2 ff = CommonFactoryFinder.getFilterFactory2();

  public static void main(String[] args) throws MalformedURLException, IOException {
    Map<String, Object> inParams = new HashMap<>();
    Map<String, Object> outParams = new HashMap<>();
    //Set up parameters for postgis (or whatever)

    inParams.put(PostgisNGDataStoreFactory.DBTYPE.key, PostgisNGDataStoreFactory.DBTYPE.sample);
    inParams.put(PostgisNGDataStoreFactory.USER.key, "username");
    inParams.put(PostgisNGDataStoreFactory.PASSWD.key, "password");
    inParams.put(PostgisNGDataStoreFactory.HOST.key, "host");
    inParams.put(PostgisNGDataStoreFactory.PORT.key, 5432);
    inParams.put(PostgisNGDataStoreFactory.SCHEMA.key, "schema");

    outParams.put(PostgisNGDataStoreFactory.DBTYPE.key, PostgisNGDataStoreFactory.DBTYPE.sample);
    outParams.put(PostgisNGDataStoreFactory.USER.key, "username");
    outParams.put(PostgisNGDataStoreFactory.PASSWD.key, "password");
    outParams.put(PostgisNGDataStoreFactory.HOST.key, "host");
    outParams.put(PostgisNGDataStoreFactory.PORT.key, 5432);
    outParams.put(PostgisNGDataStoreFactory.SCHEMA.key, "schema");

    // Read
    DataStore inputDataStore = DataStoreFinder.getDataStore(inParams);

    String inputTypeName = inputDataStore.getTypeNames()[0];
    SimpleFeatureType inputType = inputDataStore.getSchema(inputTypeName);

    FeatureSource<SimpleFeatureType, SimpleFeature> source = inputDataStore.getFeatureSource(inputTypeName);

    // Write

    DataStore newDataStore = DataStoreFinder.getDataStore(outParams);

    newDataStore.createSchema(inputType);
    String typeName = newDataStore.getTypeNames()[0];
    SimpleFeatureStore featureStore = (SimpleFeatureStore) newDataStore.getFeatureSource(typeName);

    //filter
    String geometryPropertyName = inputType.getGeometryDescriptor().getLocalName();
    CoordinateReferenceSystem targetCRS = inputType.getGeometryDescriptor().getCoordinateReferenceSystem();

    double x1 = 11.5;
    double y1 = 49.8;
    double x2 = 12.0;
    double y2 = 50.1;

    ReferencedEnvelope bbox = new ReferencedEnvelope(x1, y1, x2, y2, targetCRS);

    Filter filter = ff.bbox(ff.property(geometryPropertyName), bbox);
    // write results
    featureStore.addFeatures(source.getFeatures(filter));
    //tidy up
    inputDataStore.dispose();
    newDataStore.dispose();
  }

}