0

Because of my problem with spatial queries (INTERSECTS) not working properly on a layer with SRID = -1 created with ogr2ogr - I need to force SRID of the geometry attribute of the layer to be automatically guesed or set it through the code during or after the layer creation process:

SRID -1(minus one)

SQL View of the table:

SELECT * FROM "e2b0ad81_8888_4cd4_b455_6060e4cbea3e"

When I do "Guess geometry type and srid" here (GeoServer admin panel) - it guesses SRID right, ao I think it could be done automatically somehow

I found a messed up way around the problem:

Turned out that config that specifies the SRID field lays not in the DB but inside GeoServer container itself - somwhere like:

/opt/services/storage/%BD name?%/geoserver/data_dir/workspaces/%BD user?%/database/%LAYER NAME%/featuretype.xml

And after adding/modifiyng (top lavel) this to the XML file:

<geometry>
    <name>the_geom</name>
    <type>Geometry</type>
    <srid>3857</srid>
</geometry>

And after restarting container with GeoServer - the changes aplayes and spatial queries work

Is there any better way to do this?

Preferably without restarting anything

Layer ogr2ogr command:

ogr2ogr -progress -overwrite -nln e2b0ad81_8888_4cd4_b455_6060e4cbea3e -t_srs EPSG:3857 -lco GEOMETRY_NAME=the_geom -lco FID=fid -dialect OGRSQL -sql "%ATTRS CREATION SQL% string" -s_srs EPSG:3857 PG:host=%HOST% port=%PORT% user=%USER% dbname=%DBNAME% password=%PW% 'sridtest.gpkg'

Geometry attribute SQL creation line:

the_geom geometry(Point,3857),

Full layer table SQL creation script:

CREATE TABLE IF NOT EXISTS public."e2b0ad81_8888_4cd4_b455_6060e4cbea3e"
(
    fid integer NOT NULL DEFAULT nextval('"e2b0ad81_8888_4cd4_b455_6060e4cbea3e_fid_seq"'::regclass),
    attr_0 character varying COLLATE pg_catalog."default",
    ...
    attr_21 character varying COLLATE pg_catalog."default",
    
    the_geom geometry(Point,3857),
    
    CONSTRAINT "e2b0ad81_8888_4cd4_b455_6060e4cbea3e_pkey" PRIMARY KEY (fid)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."e2b0ad81_8888_4cd4_b455_6060e4cbea3e"
    OWNER to mapuser;

REVOKE ALL ON TABLE public."e2b0ad81_8888_4cd4_b455_6060e4cbea3e" FROM replicator;

GRANT ALL ON TABLE public."e2b0ad81_8888_4cd4_b455_6060e4cbea3e" TO mapuser;

GRANT SELECT ON TABLE public."e2b0ad81_8888_4cd4_b455_6060e4cbea3e" TO replicator;
-- Index: e2b0ad81_8888_4cd4_b455_6060e4cbea3e_geom_geom_idx

-- DROP INDEX IF EXISTS public."e2b0ad81_8888_4cd4_b455_6060e4cbea3e_geom_geom_idx";

CREATE INDEX IF NOT EXISTS "e2b0ad81_8888_4cd4_b455_6060e4cbea3e_geom_geom_idx"
    ON public."e2b0ad81_8888_4cd4_b455_6060e4cbea3e" USING gist
    (the_geom)
    TABLESPACE pg_default;

Versions:

GeoServer 2.17.2 
GeoTools 23.2 (rev 3a74d70c88a384047c14ebcb77420d0bd0ba0fc2) 
GeoWebCache 1.17.2 (rev 1.17.x/b5344b575976fe578594157fb9a90cb399f8b272)
Improve this question
6
  • is this an SQL View? as there should be no need to "guess" the SRID of a well constructed table.
    – Ian Turton ♦
    Jan 19 at 9:32
  • @IanTurton, the screenshot is SQL View, yes. How can I fix the way my table is constructed to make it work well?
    – Eugene Kuklin
    Jan 19 at 10:35
  • Then we need to see the SQL you are using, the table is irrelevant and can be removed from the question.
    – Ian Turton ♦
    Jan 19 at 10:38
  • @IanTurton, I added SQL View of the table. I think the table itself may help someone with the similar problem to understand what I'm talking about by visual reference.
    – Eugene Kuklin
    Jan 19 at 10:51
  • I suspect that your view is not being picked up in the geometry_columns view so GeoServer can't tell what the type and srid of the geometry is. I'm not sure if the CTE is what's fooling it, can you try without it
    – Ian Turton ♦
    Jan 19 at 11:01

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy