Selecting a start line with where geom returns different geom

user_2123174 0
Viewed 43

I have a table with multilinestrings and I would like to find a line which starts at a certain point. I am using: 

gis=>  select * from (select gid, ST_StartPoint(ST_LineMerge((geom))) as start from aw_pipes) s where s.start = '0101000020E6100000D050E433E678D73F71F37608AF284A40' order by gid;
 gid  |                       start
------+----------------------------------------------------
 1458 | 0101000020E6100000809BB933E678D73FE3D07608AF284A40
(1 row)

The problem here is the result has different geom. It is not what I asked for. It doesn't event contain the geom I was searching for.

gis=> select geom from aw_pipes where gid = 1458;
                                                     geom
--------------------------------------------------------------------------------------------------------------
 0105000020E610000001000000010200000002000000809BB933E678D73FE3D07608AF284A40CDD4B35E4E7BD73F62DB7D83AD284A40
(1 row)

gis=> select ST_DumpPoints('0105000020E610000001000000010200000002000000809BB933E678D73FE3D07608AF284A40CDD4B35E4E7BD73F62DB7D83AD284A40');
                        st_dumppoints
--------------------------------------------------------------
 ("{1,1}",0101000020E6100000809BB933E678D73FE3D07608AF284A40)
 ("{1,2}",0101000020E6100000CDD4B35E4E7BD73F62DB7D83AD284A40)
(2 rows)

My postgis version is:

gis=> select PostGIS_Version ();
            postgis_version
---------------------------------------
 2.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

Any idea what I am doing wrong or any suggestion what I may change in my query?

postgis where-clause select sql
1 Answers

Comparing geometries using = might not lead to the result you would expect. Before PostGIS 2.4, the equality is based on the geometry bounding box, not on the vertices themselves, and likely truncate insignificant digits. Check this excellent article.

You could replace = by ST_Equals, which checks every vertices for exact spatial equality.

That being said, your expectations might be a bit too high. The distance between your geometry and the line starting point is 1.266e-005 m, so about 10 micrometers... most GIS user/software would consider the two points as being equal.

user_76355690
Related