<html>
<head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hey all, i follow your answer Remi, but do this because your code
come to me late:<br>
<br>
--part 1<br>
DROP TABLE IF EXISTS all_lines;<br>
CREATE TABLE all_lines AS<br>
SELECT ST_ExteriorRing((ST_Dump(z.the_geom)).geom) AS the_geom<br>
FROM public.rad_solar_global z<br>
UNION ALL<br>
SELECT ST_ExteriorRing((ST_Dump(r.the_geom)).geom) AS the_geom<br>
FROM public.rad_solar_plano_inclinado r;<br>
UPDATE all_lines SET the_geom=ST_SetSRID(the_geom,4326);<br>
SELECT Populate_Geometry_Columns('public.all_lines'::regclass);<br>
ALTER TABLE all_lines ADD column gid serial;<br>
ALTER TABLE all_lines ADD PRIMARY KEY (gid);<br>
<br>
<br>
--part 2<br>
DROP TABLE IF EXISTS all_lines_MERGED;<br>
CREATE TABLE all_lines_MERGED AS<br>
(SELECT ST_LineMerge(k.the_geom) AS the_geom<br>
FROM public.all_lines k);<br>
UPDATE all_lines_MERGED SET the_geom=ST_SetSRID(the_geom,4326);<br>
SELECT
Populate_Geometry_Columns('public.all_lines_MERGED'::regclass);<br>
ALTER TABLE all_lines_MERGED ADD column gid serial;<br>
ALTER TABLE all_lines_MERGED ADD PRIMARY KEY (gid);<br>
<br>
--part 3<br>
DROP TABLE IF EXISTS rad_solar_directa_SPLITED;<br>
CREATE TABLE rad_solar_directa_SPLITED AS<br>
(SELECT ST_Split(b.the_geom,k.the_geom) AS the_geom<br>
FROM public.rad_solar_directa b,public.all_lines_MERGED k);<br>
UPDATE rad_solar_directa_SPLITED SET
the_geom=ST_SetSRID(the_geom,4326);<br>
SELECT
Populate_Geometry_Columns('public.rad_solar_directa_SPLITED'::regclass);<br>
ALTER TABLE rad_solar_directa_SPLITED ADD column gid serial;<br>
ALTER TABLE rad_solar_directa_SPLITED ADD PRIMARY KEY (gid);<br>
<br>
<br>
--i just have made part 1,2 and am still in part 3, am there since
2 days ago, is there a way to know if this is working fine or if it
fall in an unfinish error. am still in part 3, is that posible?
help please.<br>
<br>
Cheers,<br>
Ale<br>
<br>
<div class="moz-cite-prefix">El 14/12/13 17:19, Rémi Cura escribió:<br>
</div>
<blockquote
cite="mid:CAJvUf_t962XSA1u+zMiq-hSWL7LkzCQ68r4f2L7UsxvvUZaotg@mail.gmail.com"
type="cite">
<div dir="ltr">
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>OK,<br>
</div>
with luck you won't have precision
issues.<br>
<br>
</div>
<div>(please note that the only good
way to do this is to use
postgis_topology and faces.)<br>
</div>
<div><br>
</div>
So here is one idea to do it : <br>
</div>
table1 : first image, very big shape<br>
</div>
table 2 : second image, smal rectangular
shape<br>
</div>
table 3 third image , mdium irregular shape<br>
<br>
</div>
1.) convert poly from table 1 and table 3 to
linestring. (i called these lines dumped_lines
for the following)<br>
</div>
You can do it manually or use the function I
wrote (beta) <br>
<a moz-do-not-send="true"
href="https://github.com/Remi-C/PPPP_utilities/blob/master/postgis/rc_DumpLines.sql">https://github.com/Remi-C/PPPP_utilities/blob/master/postgis/rc_DumpLines.sql</a>
<br>
<br>
</div>
2.) split poly from table 2 by the dumped_lines
from table 1 (<a moz-do-not-send="true"
href="http://postgis.net/docs/ST_Split.html">http://postgis.net/docs/ST_Split.html</a>)
. The result are polygons<br>
<br>
</div>
3.) Split the result from 2. by dumpl_lines from
table 3. The result are polygons.<br>
<br>
</div>
3. is what you want geometrically. I'll call it
splited_polygons for the following<br>
<br>
</div>
Now you want to get, for each splitted_polygons, the id
of polygons of table 1, and 3 which overlaps. You
already have id from table 2 because you can keep it
during computing of 2. and 3.<br>
<br>
</div>
you can do it sequentially :<br>
<br>
</div>
SELECT DISTINCT ON (sp.geom) sp.geom, aray_agg(id) AS
id_table1<br>
</div>
FROM splited_polygons AS sp, table_1 <br>
</div>
<div>WHERE ST_Overlaps(sp.geom, table_1.geom)=TRUE<br>
<br>
</div>
<div>, then the same type of query on result, with table 3<br>
<br>
</div>
<div>Cheers,<br>
Rémi-C<br>
</div>
<div>
<div>
<div>
<div>
<div>
<div>
<div><br>
<div>
<div><br>
<div>
<div>
<div>
<div>
<div><br>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<div class="gmail_extra"><br>
<br>
<div class="gmail_quote">2013/12/12 <span dir="ltr"><<a
moz-do-not-send="true"
href="mailto:islanis@infomed.sld.cu" target="_blank">islanis@infomed.sld.cu</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">I want to
create little pieces of multipolygons from these 3 shapes
(A,B and C)=result , result with all the sections formed
with the intersection from the 3 shapes together, come on
tell me , do you understand now what i want<br>
thanks
<div class="HOEnZb">
<div class="h5"><br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
no never mind am not offended, am trying that you
understand Ok, here are png of my 3 multipolygon
shapes, what else do you need<br>
<br>
"Rémi Cura" <<a moz-do-not-send="true"
href="mailto:remi.cura@gmail.com" target="_blank">remi.cura@gmail.com</a>>
escribió:<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
Sorry I'm not trying to offend you.<br>
<br>
For example in this thread<br>
<a moz-do-not-send="true"
href="https://groups.google.com/forum/#%21topic/postgis-users/9pozIoUAZuI"
target="_blank">https://groups.google.com/forum/#!topic/postgis-users/9pozIoUAZuI</a><br>
The asker posted an image to explain better.<br>
<br>
Cheers,<br>
Rémi-C<br>
<br>
<br>
2013/12/12 <<a moz-do-not-send="true"
href="mailto:islanis@infomed.sld.cu"
target="_blank">islanis@infomed.sld.cu</a>><br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
no is not dificult to understand, what ever you
want i'll give , just ask<br>
what do you want to a better understanding. please
i need it<br>
<br>
"Rémi Cura" <<a moz-do-not-send="true"
href="mailto:remi.cura@gmail.com"
target="_blank">remi.cura@gmail.com</a>>
escribió:<br>
<br>
<br>
I'm very sorry it is difficult to understand what
you mean.<br>
<blockquote class="gmail_quote" style="margin:0 0
0 .8ex;border-left:1px #ccc
solid;padding-left:1ex">
Maybe with a real drawing and link here to it,
or to some screenshots?<br>
;-)<br>
<br>
Cheers,<br>
<br>
Rémi-C<br>
<br>
2013/12/12 <<a moz-do-not-send="true"
href="mailto:islanis@infomed.sld.cu"
target="_blank">islanis@infomed.sld.cu</a>><br>
<br>
thanks for the quick answer Rémi, but, thats
right thats one of answers,<br>
<blockquote class="gmail_quote" style="margin:0
0 0 .8ex;border-left:1px #ccc
solid;padding-left:1ex">
but my idea was not complete,because there is
a<br>
3rd shape, your answer works fine for the 1st
and 2dn shapes, but not<br>
with<br>
the 3rd, because the 3rd has not suares
inside,<br>
the 3rd shape have big triangles inside,the
idea is that i need some<br>
routine that merge or blend the spatial data
and with it the attributes<br>
of respective data, i need some ideas for do
that, and am working on it,<br>
but not sucess, please i need help, thanks for
all.<br>
<br>
<br>
1-the first shapefile<br>
__________________<br>
| |<br>
| |<br>
| 1 | shp=A<br>
| |<br>
| |<br>
|_________________|<br>
num of poligons=1<br>
<br>
<br>
The one big country, this is a multipolygon
shape<br>
<br>
DATA=>dni01 numeric,dni02 numeric,dni03
numeric,<br>
dni04 numeric,dni05 numeric,dni06
numeric,<br>
dni07 numeric,dni08 numeric,dni09
numeric,<br>
dni10 numeric,dni11 numeric,dni12
numeric,<br>
dniann numeric,the_geom geometry<br>
<br>
2-and the second shapefile<br>
__________________<br>
| 2 | 2 |<br>
|________|________|<br>
| 2 | 2 | shp=B<br>
|________|________|<br>
| 2 | 2 |<br>
|________|________|<br>
num of poligons = 6<br>
<br>
The same country, but this time in little
pieces, the states, this is a<br>
multipolygon shape too<br>
<br>
DATA=>dni01 numeric,dni02 numeric,dni03
numeric,<br>
dni04 numeric,dni05 numeric,dni06
numeric,<br>
dni07 numeric,dni08 numeric,dni09
numeric,<br>
dni10 numeric,dni11 numeric,dni12
numeric,<br>
dniann numeric,the_geom geometry<br>
<br>
<br>
3-and the second shapefile<br>
__________________<br>
| / |<br>
| / |<br>
| 3 / | shp=C<br>
| / |<br>
| / 4 |<br>
|_____/___________|<br>
num of poligons = 2<br>
<br>
The same country, but this time in little
pieces, the states, this is a<br>
multipolygon shape too<br>
<br>
DATA=>dni01 numeric,dni02 numeric,dni03
numeric,<br>
dni04 numeric,dni05 numeric,dni06
numeric,<br>
dni07 numeric,dni08 numeric,dni09
numeric,<br>
dni10 numeric,dni11 numeric,dni12
numeric,<br>
dniann numeric,the_geom geometry<br>
<br>
<br>
<br>
and i want join the 2 shapes in only one
shapefile getting something<br>
like<br>
this<br>
<br>
______________________________<br>
| | / |<br>
| |1,2,3 / |<br>
| 1,2,3 | /1,2,4 |<br>
| | / |<br>
|______________|____/_________|<br>
| |1,2/ | shp=A and
B<br>
| |,3/ |<br>
| 1,2,3 | / 1,2,4 |<br>
| |/ |<br>
|______________/______________|<br>
| /| |<br>
| / | |<br>
| 1,2,3 /1,| 1,2,4 |<br>
| /2,4| |<br>
|_________/____|______________|<br>
num of poligons = 9<br>
<br>
<br>
DATA=>dni01A numeric,dni02A numeric,dni03A
numeric,dni04A numeric,<br>
dni05A numeric,dni06A numeric,dni07A
numeric,dni08A numeric,<br>
dni09A numeric,dni10A numeric,dni11A
numeric,dni12A numeric,<br>
dniannA numeric,dni01B numeric,dni02B
numeric,dni03B numeric,<br>
dni04B numeric,dni05B numeric,dni06B
numeric,dni07B numeric,<br>
dni08B numeric,dni09B numeric,dni10B
numeric,dni11B numeric,<br>
dni12B numeric,dniannB numeric,dni01C
numeric,dni02C numeric,<br>
dni03C numeric,dni04C numeric,dni05C
numeric,dni06C numeric,<br>
dni07C numeric,dni08C numeric,dni09C
numeric,dni10C numeric,<br>
dni11C numeric,dni12C numeric,dniannC
numeric,the_geom geometry<br>
<br>
"Rémi Cura" <<a moz-do-not-send="true"
href="mailto:remi.cura@gmail.com"
target="_blank">remi.cura@gmail.com</a>>
escribió:<br>
<br>
<br>
Seems like If you want :<br>
<br>
<blockquote class="gmail_quote"
style="margin:0 0 0 .8ex;border-left:1px
#ccc solid;padding-left:1ex">
for each little square, get the id of big
square overlaping.<br>
If this is simply this, you don't need the
"intersection" function,<br>
but only the "intersects" function :<br>
<br>
You could do something like this<br>
<br>
SELECT <a moz-do-not-send="true"
href="http://ss.id" target="_blank">ss.id</a>,
<a moz-do-not-send="true"
href="http://bs.id" target="_blank">bs.id</a>
, ss.geom<br>
FROM smal_square_table AS ss,
big_square_table AS bs<br>
WHERE ST_Intersects(ss.geom,bs.geom)=TRUE<br>
<br>
Cheers,<br>
Rémi-C<br>
<br>
<br>
<br>
<br>
2013/12/12 <<a moz-do-not-send="true"
href="mailto:islanis@infomed.sld.cu"
target="_blank">islanis@infomed.sld.cu</a>><br>
<br>
Hi,<br>
<br>
<blockquote class="gmail_quote"
style="margin:0 0 0 .8ex;border-left:1px
#ccc solid;padding-left:1ex">
<br>
<br>
<blockquote class="gmail_quote"
style="margin:0 0 0 .8ex;border-left:1px
#ccc solid;padding-left:1ex">
Try pg 21 of this: <a
moz-do-not-send="true"
href="http://presentations.opengeo"
target="_blank">http://presentations.opengeo</a>.<br>
org/2011_FOSS4G/postgis-power.pdf<br>
<br>
That will give you the metacode.<br>
<br>
Overlays. (he sighs.) Be glad you only
have 2 layers... .<br>
<br>
Best,<br>
Steve<br>
<br>
Hey steve thanks for the book is really
good as a resume, but the<br>
only<br>
<br>
</blockquote>
apart that it have to do with my problem
is the intersection of 2<br>
shapes<br>
"SELECT<br>
a.*, b.*,<br>
ST_Intersection(a.geom, b.geom)<br>
FROM<br>
a, b<br>
WHERE<br>
ST_Intersects(a.geom, b.geom);"<br>
<br>
but my problem is different, i got as a
mention 2 shapes with the same<br>
projection and place,<br>
and are the same but one is from one
study, and the other is from other<br>
stufy, but are the same<br>
place and each one have data, the only
things that they got different<br>
are<br>
the size of the squares,the data they got
and the<br>
name of the columns are the same, but the
values are different cause<br>
the<br>
type of the study<br>
,there is one that have the squares more
smaller that the other, but i<br>
dont want lose any data, i need that in
the map it has seen like<br>
the figure "A and B" but in the data each
row got the values of A and<br>
the<br>
values of B<br>
<br>
1-the first shapefile<br>
__________________<br>
| |<br>
| |<br>
| 1 | shp=A<br>
| |<br>
| |<br>
|_________________|<br>
<br>
The one big country, this is a
multipolygon shape<br>
<br>
DATA=>dni01 numeric,dni02 numeric,dni03
numeric,<br>
dni04 numeric,dni05 numeric,dni06
numeric,<br>
dni07 numeric,dni08 numeric,dni09
numeric,<br>
dni10 numeric,dni11 numeric,dni12
numeric,<br>
dniann numeric,the_geom geometry<br>
<br>
<br>
2-and the second shapefile<br>
__________________<br>
| 2 | 2 |<br>
|________|________|<br>
| 2 | 2 | shp=B<br>
|________|________|<br>
| 2 | 2 |<br>
|________|________|<br>
The same country, but this time in little
pieces, the states, this is<br>
a<br>
multipolygon shape too<br>
<br>
DATA=>dni01 numeric,dni02 numeric,dni03
numeric,<br>
dni04 numeric,dni05 numeric,dni06
numeric,<br>
dni07 numeric,dni08 numeric,dni09
numeric,<br>
dni10 numeric,dni11 numeric,dni12
numeric,<br>
dniann numeric,the_geom geometry<br>
<br>
<br>
and i want join the 2 shapes in only one
shapefile getting something<br>
like<br>
this<br>
<br>
__________________<br>
| 1,2 | 1,2 |<br>
|________|________|<br>
| 1,2 | 1,2 | shp=A and B<br>
|________|________|<br>
| 1,2 | 1,2 |<br>
|________|________|<br>
<br>
DATA=>dni01A numeric,dni02A
numeric,dni03A numeric,dni04A numeric,<br>
dni05A numeric,dni06A numeric,dni07A
numeric,dni08A numeric,<br>
dni09A numeric,dni10A numeric,dni11A
numeric,dni12A numeric,<br>
dniannA numeric,dni01B numeric,dni02B
numeric,dni03B numeric,<br>
dni04B numeric,dni05B numeric,dni06B
numeric,dni07B numeric,<br>
dni08B numeric,dni09B numeric,dni10B
numeric,dni11B numeric,<br>
dni12B numeric,dniannB
numeric,the_geom geometry<br>
<br>
i think that this is posible to do with
postgis, but i dont know why,<br>
am<br>
looking in quantumGIS but i lose data
cause the big suare overlap the<br>
little<br>
ones and i lose data cause there is some
places that i get NULL.<br>
<br>
please help<br>
thanks for all<br>
<br>
<br>
<br>
<br>
<br>
<blockquote class="gmail_quote"
style="margin:0 0 0 .8ex;border-left:1px
#ccc solid;padding-left:1ex">
<br>
<br>
On Mon, Dec 9, 2013 at 11:03 AM,
J.Alejandro Martinez Linares <<br>
<br>
<a moz-do-not-send="true"
href="mailto:islanis@infomed.sld.cu"
target="_blank">islanis@infomed.sld.cu</a>>
wrote:<br>
<blockquote class="gmail_quote"
style="margin:0 0 0
.8ex;border-left:1px #ccc
solid;padding-left:1ex">
<br>
Hi People, i need your help, let say
that i have 2 shapefiles<br>
<br>
1-the first shapefile<br>
__________________<br>
| |<br>
| |<br>
| 1 | shp=B<br>
| |<br>
| |<br>
|_________________|<br>
<br>
One big country, this is a
multipolygon shape<br>
<br>
2-and the second shapefile<br>
__________________<br>
| 2 | 2 |<br>
|________|________|<br>
| 2 | 2 | shp=B<br>
|________|________|<br>
| 2 | 2 |<br>
|________|________|<br>
The same country, but this time in
little pieces, the states, this<br>
is<br>
a multipolygon shape too<br>
<br>
and i want join the 2 shapes in only
one shapefile getting<br>
something<br>
like this<br>
<br>
__________________<br>
| 1,2 | 1,2 |<br>
|________|________|<br>
| 1,2 | 1,2 | shp=A and B<br>
|________|________|<br>
| 1,2 | 1,2 |<br>
|________|________|<br>
<br>
i think that this is posible, please
help me to get somthing like<br>
this i need it.<br>
<br>
<br>
<br>
--<br>
<br>
Este mensaje le ha llegado mediante
el servicio de correo<br>
electronico<br>
que ofrece Infomed para respaldar el
cumplimiento de las misiones del<br>
Sistema Nacional de Salud. La persona
que envia este correo asume el<br>
compromiso de usar el servicio a tales
fines y cumplir con las<br>
regulaciones<br>
establecidas<br>
<br>
Infomed: <a moz-do-not-send="true"
href="http://www.sld.cu/"
target="_blank">http://www.sld.cu/</a><br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a moz-do-not-send="true"
href="mailto:postgis-users@lists.osgeo.org"
target="_blank">postgis-users@lists.osgeo.org</a><br>
<a moz-do-not-send="true"
href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users"
target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br>
<br>
<br>
</blockquote>
<br>
</blockquote>
<br>
<br>
----------------------------------------------------------------<br>
This message was sent using IMP, the
Internet Messaging Program.<br>
<br>
<br>
<br>
--<br>
<br>
Este mensaje le ha llegado mediante el
servicio de correo electronico<br>
que<br>
ofrece Infomed para respaldar el
cumplimiento de las misiones del<br>
Sistema<br>
Nacional de Salud. La persona que envia
este correo asume el compromiso<br>
de<br>
usar el servicio a tales fines y cumplir
con las regulaciones<br>
establecidas<br>
<br>
Infomed: <a moz-do-not-send="true"
href="http://www.sld.cu/"
target="_blank">http://www.sld.cu/</a><br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a moz-do-not-send="true"
href="mailto:postgis-users@lists.osgeo.org"
target="_blank">postgis-users@lists.osgeo.org</a><br>
<a moz-do-not-send="true"
href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users"
target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br>
<br>
<br>
</blockquote>
<br>
</blockquote>
<br>
----------------------------------------------------------------<br>
This message was sent using IMP, the Internet
Messaging Program.<br>
<br>
<br>
<br>
--<br>
<br>
Este mensaje le ha llegado mediante el
servicio de correo electronico que<br>
ofrece Infomed para respaldar el cumplimiento
de las misiones del Sistema<br>
Nacional de Salud. La persona que envia este
correo asume el compromiso<br>
de<br>
usar el servicio a tales fines y cumplir con
las regulaciones<br>
establecidas<br>
<br>
Infomed: <a moz-do-not-send="true"
href="http://www.sld.cu/" target="_blank">http://www.sld.cu/</a><br>
<br>
<br>
<br>
</blockquote>
<br>
</blockquote>
<br>
<br>
----------------------------------------------------------------<br>
This message was sent using IMP, the Internet
Messaging Program.<br>
<br>
<br>
<br>
--<br>
<br>
Este mensaje le ha llegado mediante el servicio de
correo electronico que<br>
ofrece Infomed para respaldar el cumplimiento de
las misiones del Sistema<br>
Nacional de Salud. La persona que envia este
correo asume el compromiso de<br>
usar el servicio a tales fines y cumplir con las
regulaciones establecidas<br>
<br>
Infomed: <a moz-do-not-send="true"
href="http://www.sld.cu/" target="_blank">http://www.sld.cu/</a><br>
<br>
<br>
</blockquote>
<br>
</blockquote>
<br>
<br>
----------------------------------------------------------------<br>
This message was sent using IMP, the Internet
Messaging Program.<br>
<br>
<br>
--<br>
<br>
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<br>
<br>
Infomed: <a moz-do-not-send="true"
href="http://www.sld.cu/" target="_blank">http://www.sld.cu/</a><br>
<br>
<br>
<br>
</blockquote>
<br>
<br>
<br>
----------------------------------------------------------------<br>
This message was sent using IMP, the Internet Messaging
Program.<br>
<br>
<br>
--<br>
<br>
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<br>
<br>
Infomed: <a moz-do-not-send="true"
href="http://www.sld.cu/" target="_blank">http://www.sld.cu/</a><br>
<br>
<br>
</div>
</div>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a moz-do-not-send="true"
href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a moz-do-not-send="true"
href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users"
target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
</blockquote>
</div>
<br>
</div>
</blockquote>
<br>
</body>
</html>