[postgis-users] getting unexpected results on an ST_Intersectsquery

Mon Apr 7 20:57:26 PDT 2014

Hugues, i could solve my problem with an external tool, not with 
postgis, i made it with openjump's superposition, thanks for all.

El 05/04/14 23:04, Hugues François escribió:
> 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 : islanis at infomed.sld.cu [mailto:islanis at infomed.sld.cu]
> 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" <hugues.francois at irstea.fr> 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 : postgis-users-bounces at lists.osgeo.org
>> [mailto:postgis-users-bounces at lists.osgeo.org] De la part de
>> islanis at infomed.sld.cu Envoyé : samedi 5 avril 2014 21:28 À :
>> postgis-users at lists.osgeo.org 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
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> 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 : islanis at infomed.sld.cu [mailto:islanis at infomed.sld.cu]
> 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" <hugues.francois at irstea.fr> 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 : postgis-users-bounces at lists.osgeo.org
>> [mailto:postgis-users-bounces at lists.osgeo.org] De la part de
>> islanis at infomed.sld.cu Envoyé : samedi 5 avril 2014 21:28 À :
>> postgis-users at lists.osgeo.org 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
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> 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
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

--

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/