I have geojson derived from a catchment API stored in a postgres. Using a st_transform function a geometry field is added in a view as per the code below. The resulting view works fine in QGIS however it fails to load when added to ArcMap /Pro. The issue only happens when features contain complex polygons (donut shapes), no issues when the polygon is simple.
This issue seems recent, as I have been using this for over three years now and always the complex polygons were visible within Arc. as anything changed recently?
I can share actual data if that helps.
CREATE OR REPLACE VIEW public.travel_times_test
AS
WITH data AS (
SELECT travel_data_new.id,
travel_data_new.type,
travel_data_new."time",
travel_data_new.postcode,
travel_data_new.project,
travel_data_new.data::json AS fc
FROM travel_data_new
)
SELECT row_number() OVER () AS objectid,
f.id,
f.postcode,
f.type,
f.project,
f."time",
st_transform(st_setsrid(st_geomfromgeojson(f.feat ->> 'geometry'::text), 4326), 3785) AS geom
FROM ( SELECT json_array_elements(data.fc -> 'features'::text) AS feat,
data.id,
data.type,
data."time",
data.project,
data.postcode
FROM data) f;
ALTER TABLE public.travel_times_test
OWNER TO gwkgis2016;
Actually used this method to generate the geometry: Storing GeoJSON FeatureCollection to PostgreSQL with PostGIS? - Geographic Information Systems Stack...
SG_ZERO_AREA_POLYGON (-2032) Polygon shell has no area
Are you sure this was related to donut shapes and not empty geometries? I am encountering this issue (above code) at a low-percentage rate when dealing with data sourced from Open Street Maps.
When I take a close look at them they all have one thing in common: All x- or y-coordinates of a multi-part polygon share the same value (see image). Thus leading to a line aka empty polygon.
I am trying to insert into a PostgreSQL Enterprise Geodatabase (using st_geometry) but these records fail. Anything I can do here to get them to load in? I.e. casting or transforming...? Thinking of looping through and checking the area value of each polygon within a multi-polygon. If 0, then remove it, so at least the rest of the multi-polygon loads in and it doesn't prevent the whole insert.
Here are a few offenders: