2

I'm trying to insert a new line in a big table with the following SQL using PostgreSQL 9.3 and PostGis 2.1.3:

INSERT INTO "Edigeo"."GEOM"(
            cnig, geom_surf,  
           etat, codcom)
    VALUES  ('H_11_4_6',
        ST_Difference((SELECT "GEOM_1".geom_surf FROM "Edigeo"."GEOM" AS "GEOM_1" WHERE "GEOM_1".codcom = '73101' AND "GEOM_1".cnig = 'H_1_6_0' LIMIT 1),(SELECT ST_Union("GEOM_2".geom_surf) FROM "Edigeo"."GEOM" AS "GEOM_2" WHERE "GEOM_2".codcom = '73101' AND "GEOM_2".cnig IN ('H_11_4_0','D_1_0_8')  AND ST_IsValid("GEOM_2".geom_surf) = True)),
        NULL,
        '73101')

my text field are filled without problem but i have an issue to fill the geometry column (geom_surf) with the ST_Difference statement... It remains empty in the table after the execution.

If i do the same in a select statement, the geometry seems ok.

SELECT ST_Difference((SELECT "GEOM_1".geom_surf FROM "Edigeo"."GEOM" AS "GEOM_1" WHERE "GEOM_1".codcom = '73101' AND "GEOM_1".cnig = 'H_1_6_0' LIMIT 1),(SELECT ST_Union("GEOM_2".geom_surf) FROM "Edigeo"."GEOM" AS "GEOM_2" WHERE "GEOM_2".codcom = '73101' AND "GEOM_2".cnig IN ('H_11_4_0','D_1_0_8')  AND ST_IsValid("GEOM_2".geom_surf) = True))

returns

enter image description here

So it looks nice.

But in my table after the insert:enter image description here

Is there an issue with my geometry? or something else? I don't understand why it's not working...

  • Please Edit the question to specify the exact versions of PostgreSQL and PostGIS, include the table creation SQL, and include an actual query that uses the newly inserted row (sometimes pgAdmin doesn't show large geometries). – Vince Jun 21 '17 at 11:09
10

For large geometries pgAdmin will show a blank field even though there is actually data there. You can verify the existence of data by checking for null geometries:

select * from "Edigeo"."GEOM" where geom_surf is NULL

This tip from the PostGIS website explains in a bit more detail and includes examples of other checks you can carry out.

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.