Re: [postgis-users] Diff. Between 2 tables
On 06/07/2012 09:15 AM, Hemin Tofiq wrote: Hi, I would like to ask if there is a way to compare (find the difference) between 2 tables? Updated geometries or attributes), i.e. An sql som returnes the rows that are updated. To compare and sync database tables You can use very nice tool called 'pg_comparator'. -- Ivan Mincik ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Diff. Between 2 tables
Hi, I would like to ask if there is a way to compare (find the difference) between 2 tables? Updated geometries or attributes), i.e. An sql som returnes the rows that are updated. Thanks in advance, Hemin -- View this message in context: http://postgis.17.n6.nabble.com/Diff-Between-2-tables-tp4998062.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Diff. Between 2 tables
Hello, I'm not sure I understand the whole thing but in a basic way and if your table have a primary key a self join should help you : select a.* from mytable a, mytable b where a.id = b.id and (a.attr1 != b.attr1 or a.attr2 != b.attr2) The attribut can be a geometry or anything else. Hugues -Message d'origine- De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de Hemin Tofiq Envoyé : jeudi 7 juin 2012 09:16 À : postgis-users@postgis.refractions.net Objet : [postgis-users] Diff. Between 2 tables Hi, I would like to ask if there is a way to compare (find the difference) between 2 tables? Updated geometries or attributes), i.e. An sql som returnes the rows that are updated. Thanks in advance, Hemin -- View this message in context: http://postgis.17.n6.nabble.com/Diff-Between-2-tables-tp4998062.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Diff. Between 2 tables
Thank you, I have 2 tables with the same structure, so we don't need to create alias for the same table, however that is fine with updated rows, how can I get newly created rows? Regards, Hemin -- View this message in context: http://postgis.17.n6.nabble.com/Diff-Between-2-tables-tp4998062p4998074.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Diff. Between 2 tables
On 06/07/2012 09:14 AM, Hemin Tofiq wrote: Thank you, I have 2 tables with the same structure, so we don't need to create alias for the same table, however that is fine with updated rows, how can I get newly created rows? Regards, Hemin I assume you mean you have added a new row to one table but not the "twin" table. Comparing on a unique key would look like: SELECT * FROM table1 LEFT JOIN table2 ON (table1.unique_key = table2.unique_key) WHERE table2.unique_key IS NULL; I think you said you want to be able to do this on geometries. I haven't tried this, but I assume something like this might work: SELECT * FROM table1 LEFT JOIN table2 ON (ST_Equals(table1.the_geom, table2.the_geom)) WHERE table2.the_geom IS NULL; Just make sure to read the docs on ST_Equals vs. ST_OrderingEquals so that you know which comparison you want. http://postgis.refractions.net/documentation/manual-1.5/ST_Equals.html http://postgis.refractions.net/documentation/manual-1.5/ST_OrderingEquals.html Best, --Lee -- Lee Hachadoorian PhD, Earth Environmental Sciences (Geography) Research Associate, CUNY Center for Urban Research http://freecity.commons.gc.cuny.edu ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Diff. Between 2 tables
You could also try with not exists select * from table2 where not exists (select unique_key from table1 where table1.unique_key = table2.unique_key) Hugues. De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de Lee Hachadoorian Envoyé : jeudi 7 juin 2012 16:31 À : postgis-users@postgis.refractions.net Objet : Re: [postgis-users] Diff. Between 2 tables On 06/07/2012 09:14 AM, Hemin Tofiq wrote: Thank you, I have 2 tables with the same structure, so we don't need to create alias for the same table, however that is fine with updated rows, how can I get newly created rows? Regards, Hemin I assume you mean you have added a new row to one table but not the twin table. Comparing on a unique key would look like: SELECT * FROM table1 LEFT JOIN table2 ON (table1.unique_key = table2.unique_key) WHERE table2.unique_key IS NULL; I think you said you want to be able to do this on geometries. I haven't tried this, but I assume something like this might work: SELECT * FROM table1 LEFT JOIN table2 ON (ST_Equals(table1.the_geom, table2.the_geom)) WHERE table2.the_geom IS NULL; Just make sure to read the docs on ST_Equals vs. ST_OrderingEquals so that you know which comparison you want. http://postgis.refractions.net/documentation/manual-1.5/ST_Equals.html http://postgis.refractions.net/documentation/manual-1.5/ST_OrderingEquals.html Best, --Lee -- Lee Hachadoorian PhD, Earth Environmental Sciences (Geography) Research Associate, CUNY Center for Urban Research http://freecity.commons.gc.cuny.edu ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Diff. Between 2 tables
Thank you for your answers. -- View this message in context: http://postgis.17.n6.nabble.com/Diff-Between-2-tables-tp4998062p4998090.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
