1

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 1

Figure 2 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.

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();
  }

}
  • Dear Ian, thanks for your support! Unfortunately I am not allowed to use any libraries except the JAVA standard libraries. Because of that I am still looking for a fitting solution (approach). Maybe you have any other ideas? If not, thank you for your help! – pier Apr 23 '18 at 14:51
  • JTS isn't a standard library – Ian Turton Apr 23 '18 at 15:01
  • I should correct my statement: I am only allowed to use JAVA standard libraries inclusive JTS. – pier Apr 23 '18 at 18:24
  • Makes no sense to me! – Ian Turton Apr 23 '18 at 18:25
  • Yes, I agree, but these are the requirements. – pier Apr 23 '18 at 18:27

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.