I followed many question posted on the same subject but unable to solve my problem.

I have same kind of tables in 2 different database (tables with linestring geometry), I need to update id from one table to another when it finds equal geometry in another table.

table1 from DB1 looks like bellow.

     Column      |           Type           |
 old_id          | bigint                   | 
 road_id         | bigint                   | 
 road_name       | character varying(255)   | 
 geo             | geometry(Geometry,4326)  |
    "roads_final_pkey" PRIMARY KEY, btree ("old_id")
    "roads_final_wkb_geometry_geom_idx" gist (geo)

and table2 of DB2 is as bellow.

   Column    |          Type           |
 id          | bigint                  |
 object_id   | bigint                  |
 geoproperty | geometry(Geometry,4326) |
    "geofeature_pkey" PRIMARY KEY, btree (id)
    "geoproperty_idx" gist (geoproperty)
    "object_id_idx" btree (object_id)

Before updating I just checked how many of them are matching, like bellow.

select count(*) from (select * from dblink('port=5432 dbname=db2 user=usr password=pass','SELECT geoproperty from table2' as t(geoproperty Geometry)) r,table1 t1 where st_equals(t1.geo,r.geoproperty);

But it is returning only 10-15% of matches where I was expecting more than 90% match because they are same geometry copied to DB2 and updated object_id.

I also tried with st_snaptogrid,st_buffer and st_astext with geo columns, But they are taking huge amount of time compared to regular st_equals method.

But when I cross checked with non matched id's geometry from table1 with geometry in table2 with normal

select * from st_equals(st_geomfromtext('LINESTRING(.line from table1.)'),st_geomfromtext('LINESTRING(.line from table2.)'))

It is returning true but not matching with first (count/select) query.

Is there anything I am missing ?

  • Is there a significant difference in postgis versions between both databases?
    – tilt
    Oct 6 '16 at 7:25
  • No. I am using postgis 2.1 in both databases. Does change in postgis makes any changes to st_eqaul operation?
    – Aparichith
    Oct 6 '16 at 7:31
  • Change in st_equal wouldn't matter, but there could be a tiny chance on small differences in the way Geometry is being described. Also I am not sure what happens when sending data in a DBLink, did you try copying the whole (or part of) the table into the other database first?
    – tilt
    Oct 6 '16 at 7:50
  • 3
    Comparing geometries as text is not reliable. Slightly different binary geometries may convert into same WKT because of the floating point inaccuracy.
    – user30184
    Oct 6 '16 at 9:59
  • 2
    Use ST_SnapToGrid possibly? I wrote this answer some time ago which may be of some help. Oct 6 '16 at 10:24

Your Answer

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

Browse other questions tagged or ask your own question.