I don't see any picture but it seems to me some ft_cubo's polygons intersects
more than one polygon from sigfre_cub_adm_munic. You have to find the good
query to keep the good one. You could use st_area(st_intersection()) to
achieve that
UPDATE
ft_cubo
SET
gidmunic = mgid
FROM (
WITH a AS (
SELECT DISTINCT ft_cubo.gid AS cgig, sigfre_cub_adm_munic.gid AS mgid ,
ST_area(ST_intersection(ft_cubo.the_geom, sigfre_cub_adm_munic.the_geom) AS
inter_area
FROM sigfre_cub_adm_munic, ft_cubo
WHERE ST_Intersects(ft_cubo.the_geom,sigfre_cub_adm_munic.the_geom)
),
inter_max AS (
SELECT cgid, max(inter_area) AS max_inter
FROM a
GROUP BY cgid
)
SELECT a.*
FROM a
JOIN inter_max ON a.cgid = inter_max.cgid AND max_inter = inter_area
) AS foo
WHERE gid = cgid
Hugues.
-----Message d'origine-----
De : [email protected] [mailto:[email protected]]
Envoyé : samedi 5 avril 2014 22:50
À : PostGIS Users Discussion; Hugues François
Objet : Re: [postgis-users] getting unexpected results on an ST_Intersectsquery
"Hugues François" <[email protected]> escribió:
Hello,
It's difficult to give an answer since you don't tell us a lot about
unexpected results buy you could try something like (will not work if
a ft_cubo's polygon intersects different sigfre_cub_adm_munic
polygons) :
UPDATE
ft_cubo
SET
gidmunic = mgid
FROM (
SELECT DISTINCT ft_cubo.gid AS cgig, sigfre_cub_adm_munic.gid AS mgid
FROM sigfre_cub_adm_munic, ft_cubo WHERE
ST_Intersects(ft_cubo.the_geom,sigfre_cub_adm_munic.the_geom)
) AS foo
WHERE gid = cgid
this neither work, is doing the same thing, look to the picture, there are 2
towns, one is divided in many pieces, but this town is still the same town and
this towns id, is the same that the towns id of one piece picture town. i need
to do this. please
HTH
If it's not give the expected results, you can work on the subselect
query to return the expected unique gid pairs.
Hugues.
-----Message d'origine-----
De : [email protected]
[mailto:[email protected]] De la part de
[email protected] Envoyé : samedi 5 avril 2014 21:28 À :
[email protected] Objet : [postgis-users] getting
unexpected results on an ST_Intersects query
Hi folks, I'm having problems when I try to run a simple query to
update the gidmunic field of ft_cubo layer, and it assigns a value
that does not exists in said field, please, i need help , the two
layers are multipolygon type. what should I do?
UPDATE
ft_cubo
SET
gidmunic = sigfre_cub_adm_munic.gid
FROM sigfre_cub_adm_munic
WHERE
ST_Intersects(ft_cubo.the_geom,sigfre_cub_adm_munic.the_geom);
thanks in advance
----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.
--
Este mensaje le ha llegado mediante el servicio de correo electronico
que ofrece Infomed para respaldar el cumplimiento de las misiones del
Sistema Nacional de Salud. La persona que envia este correo asume el
compromiso de usar el servicio a tales fines y cumplir con las
regulaciones establecidas
Infomed: http://www.sld.cu/
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.
--
Este mensaje le ha llegado mediante el servicio de correo electronico que
ofrece Infomed para respaldar el cumplimiento de las misiones del Sistema
Nacional de Salud. La persona que envia este correo asume el compromiso de usar
el servicio a tales fines y cumplir con las regulaciones establecidas
Infomed: http://www.sld.cu/
-----Message d'origine-----
De : [email protected] [mailto:[email protected]]
Envoyé : samedi 5 avril 2014 22:50
À : PostGIS Users Discussion; Hugues François
Objet : Re: [postgis-users] getting unexpected results on an ST_Intersectsquery
"Hugues François" <[email protected]> escribió:
Hello,
It's difficult to give an answer since you don't tell us a lot about
unexpected results buy you could try something like (will not work if
a ft_cubo's polygon intersects different sigfre_cub_adm_munic
polygons) :
UPDATE
ft_cubo
SET
gidmunic = mgid
FROM (
SELECT DISTINCT ft_cubo.gid AS cgig, sigfre_cub_adm_munic.gid AS mgid
FROM sigfre_cub_adm_munic, ft_cubo WHERE
ST_Intersects(ft_cubo.the_geom,sigfre_cub_adm_munic.the_geom)
) AS foo
WHERE gid = cgid
this neither work, is doing the same thing, look to the picture, there are 2
towns, one is divided in many pieces, but this town is still the same town and
this towns id, is the same that the towns id of one piece picture town. i need
to do this. please
HTH
If it's not give the expected results, you can work on the subselect
query to return the expected unique gid pairs.
Hugues.
-----Message d'origine-----
De : [email protected]
[mailto:[email protected]] De la part de
[email protected] Envoyé : samedi 5 avril 2014 21:28 À :
[email protected] Objet : [postgis-users] getting
unexpected results on an ST_Intersects query
Hi folks, I'm having problems when I try to run a simple query to
update the gidmunic field of ft_cubo layer, and it assigns a value
that does not exists in said field, please, i need help , the two
layers are multipolygon type. what should I do?
UPDATE
ft_cubo
SET
gidmunic = sigfre_cub_adm_munic.gid
FROM sigfre_cub_adm_munic
WHERE
ST_Intersects(ft_cubo.the_geom,sigfre_cub_adm_munic.the_geom);
thanks in advance
----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.
--
Este mensaje le ha llegado mediante el servicio de correo electronico
que ofrece Infomed para respaldar el cumplimiento de las misiones del
Sistema Nacional de Salud. La persona que envia este correo asume el
compromiso de usar el servicio a tales fines y cumplir con las
regulaciones establecidas
Infomed: http://www.sld.cu/
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.
--
Este mensaje le ha llegado mediante el servicio de correo electronico que
ofrece Infomed para respaldar el cumplimiento de las misiones del Sistema
Nacional de Salud. La persona que envia este correo asume el compromiso de usar
el servicio a tales fines y cumplir con las regulaciones establecidas
Infomed: http://www.sld.cu/
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
