incorrect results with st_intersects

Ulises — Thu, 23 Jan 2014 16:23:47 GMT

Hopefully this is an easy one...

Using st_intersects I'm trying to get the records from a polygon layer which centroids intersect with another polygon layer (administrative boundaries). So far I managed to get this with the following statement...

Select p.pid,p.oldpid,m.muni,m.region
from parcels p, munici m
where sde.st_intersects(m.shape,sde_centroid(p.shape))=1
and rownum<40;

the results are not correct. It returned pins that intersect different administratives boundaries but listed to the same boundary. In other words...

"242-083-125-07";"242-000-007-36";"AGUADILLA";"AGUADILLA"
"197-070-419-10";"197-070-069-49";"AGUADILLA";"AGUADILLA"
"115-092-802-AV";"115-092-802-AV";"AGUADILLA";"AGUADILLA"
"045-100-185-58";"045-000-010-88";"AGUADILLA";"AGUADILLA"
"";"115-092-802-15";"SAN JUAN";"AGUADILLA";"AGUADILLA"
"168-005-001-20";"168-005-001-20";"AGUADILLA";"AGUADILLA"

I tried the same in my Postgis database (not using ESRI) and the results appear to be correct. I used the following in postgis...

SELECT p.pid,p.oldpid,m.muni,m.region
FROM parcels p,munic m
WHERE st_intersects (m.geom,st_centroid(p.geom)) limit 38;

results:

"242-083-125-07";"242-000-007-36";"JAYUYA";"PONCE"
"197-070-419-10";"197-070-069-49";"AGUAS BUENAS";"CAGUAS"
"115-092-802-AV";"115-092-802-AV";"SAN JUAN";"SAN JUAN"
"045-100-185-58";"045-000-010-88";"AGUADILLA";"AGUADILLA"
"";"115-092-802-15";"SAN JUAN";"SAN JUAN"
"168-005-001-20";"168-005-001-20";"COROZAL";"BAYAM�?N"

Could it be something wrong in the statement or maybe something else??? Any suggestions are appreciated...

Re: incorrect results with st_intersects

tKasiaTuszynska — Fri, 24 Jan 2014 22:01:48 GMT

Ufeliciano,
I am weary of aliasing in PostgreSQL, the following query gave correct results:

select sde.block_groups.tract90, sde.census_tracts.tract90
from sde.block_groups , sde.census_tracts
where sde.st_intersects (sde.census_tracts.shape, sde.st_centroid(sde.block_groups.shape));

Sincerely,
Kasia

Re: incorrect results with st_intersects

Ulises — Tue, 28 Jan 2014 15:34:52 GMT

Kasia,

you experienced the same kind of irregularities outside PostgreSQL? Forgot to mentioned that the incorrect results are returned in our Oracle 11g database.

thanks

topic Re: incorrect results with st_intersects in Data Management Questions

incorrect results with st_intersects

Re: incorrect results with st_intersects

Re: incorrect results with st_intersects