3

I am trying to add GeoJSON layer using OpenLayers which is in the PostgreSQL db. When I am running query as

select ST_AsGeoJson(ST_Transform(geom,900913)) as farmcode from farmdatadd where areatbl = 26

I am getting multiple row of MultiPolygon on which I want to add as a layer on map. But only the first array of layer is added and remaining all is not appearing.

My code is:

function addGEOJSONLayer(geojson) {
            var vectorSource = new ol.source.Vector({
                  features: new ol.format.GeoJSON().readFeatures(JSON.parse(geojson[0])),
             });
            if(geojson.length > 1) {
            console.log("here");
                for(var k = 1; k < geojson.length; k++) {
                console.log(k);
                    vectorSource.addFeature(new ol.Feature(new ol.format.GeoJSON().readFeatures(JSON.parse(geojson[k]))));
                }
            }
             
            var vectorLayer = new ol.layer.Vector({
                  source: vectorSource,
                  style: styleFeature
            });
            var ext = vectorLayer.getSource().getExtent();
            map.getView().fit(ext, map.getSize());
            map.addLayer(vectorLayer);
        }

I have done the same thing in old version of Openlayer as :

if (jsonString != "") {
        //var holdingFeature = JSON.parse(jsonString);
        var holdingFeature = jsonString;
        console.log(holdingFeature);
        console.log(holdingFeature.length);
        for (var i = 0; i < holdingFeature.length; i++) { 
            console.log(holdingFeature[i]);
            vector_layer_feature.addFeatures(geojson_format.read(holdingFeature[i]));
        }
    }
    for (var j = 0; j < vector_layer_feature.features.length; j++) {
        vector_layer_feature.features[j].style = { fillColor: "#4169E1", fillOpacity: 0.5 };
    }
    map.zoomToExtent(vector_layer_feature.getDataExtent());
    vector_layer_feature.refresh({ force: true });

Any suggestions?

3
  • 1
    It would be simpler if your query requested a FeatureCollection postgis.net/docs/ST_AsGeoJSON.html – Mike Sep 14 '20 at 11:04
  • 2
    Please add some example of your GeoJSON data. – TomazicM Oct 11 '20 at 8:36
  • In chrome, with javascript, I would open up the developer tools and add a break point to the if(geojson.length > 1) line. This would allow to to see if your geojson data is missing multipolygons or if you code to add the extra multipolygons after the first row is the problem. – jgm_GIS Oct 12 '20 at 15:53
1
+50

It is not clear if the passed in geojson argument is an array of rows of TEXT representing a geometry each. This is assumed in your function. Do you ever hit the "here" console log?

As mentioned in comments, better fetch a FeatureCollection (as JSONB) from the DB directly [*]:

SELECT JSONB_BUILD_OBJECT(
         'type',     'FeatureCollection',
         'features', JSONB_AGG(ST_AsGeoJSON(fc.*, 'geom_900913')::JSONB)
       )
FROM   (
  SELECT *,
         ST_Transform(geom, 900913) AS geom_900913
  FROM   farmdatadd
  -- WHERE  ST_IsValid(ST_Transform(geom, 900913))
) AS fc
;

and parse it with

var vectorSource = new ol.source.Vector({
  features: new ol.format.GeoJSON().readFeatures(featureCollection, { featureProjection: 'EPSG:900913' }),
});

Pedantic note: as per the GeoJSON specification, geometries should be stored in EPSG:4326; you can specify a dataProjection in OpenLayers (in different locations with different precedence) when defining a vector source.


[*] Here's a neat custom ST_AsFeatureCollection packed as a set of user-defined (moving) aggregates, returning JSONB.

Usage:

SELECT ST_AsFeatureCollection(t.*, 'geom') FILTER(WHERE ST_IsValid(t.geom))
FROM   <table_expression> AS t
;

Description in comments. Disclaimer: I made them.

1

Apart from directly fetching feature collection from Postgres itself, if you have an array of Polygon/MultiPolygon you can do -

ol.proj.proj4.register(proj4);// This line is optional and required if you have different source projection, here you are registering proj4 js for projection.

const polygon = new ol.Feature({
        geometry: new ol.geom.MultiPolygon(feature.geometry.coordinates),
        exampleKey: exampleValue
});

 const src = 'EPSG:27700' //optional
 const dest = 'EPSG:3857' // optional
 polygon.getGeometry().transform(src, dest) // optional
 vectorSource.addFeature(polygon) // adding polygon to the existing source

here feature is a geojson feature, so you have to add the above code in the loop, and just pass the coordinates as I have mentioned above.

Fiddle: https://jsfiddle.net/amsanket22/19ztaerq/20/

If you have different projection you can use proj4 as I have shown. Also make sure you have style added for your multi polygon.

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.