Select to view content in your preferred language

incorrect results with st_intersects

572
2
01-23-2014 08:23 AM
Ulises
by
Frequent Contributor
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...
Ulises Feliciano Troche
0 Kudos
2 Replies
tKasiaTuszynska
Regular Contributor
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
0 Kudos
Ulises
by
Frequent Contributor
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
Ulises Feliciano Troche
0 Kudos