Re: [Qgis-user] Selecting polygons on attribute - Trying OGR
On 07/24/2012 07:07 PM, Zoltan Szecsei wrote:
On 2012/07/24 17:39, Micha Silver wrote:
On 07/24/2012 06:34 PM, Zoltan Szecsei wrote:
On 2012/07/24 17:29, Andre Joost wrote:
Am 24.07.2012 17:21, schrieb Micha Silver:
I think the problem is the attribute column name begins with a
digit. Look what
happens when I rename my column from above to 21Name:
In the Qgis query form, no error is produced, but all elements are
'found' after renaming the field name to another beginning with
numbers.
Do you mean all 1.1m, or my subset of 545663 records that start with
C016?
Can't find anywhere in QGis to change the fields name :-(
Use the Table Manager plugin. (or just open the dbf in OpenOffice and
change *only* the name of the column)
Which is the Table Manager plugin? I'm just browsing QGIS plugins
repository and cannot see anything like this.
I tried with OpenOffice 3.3 on both 64 & 32 bit Win 7, but the job
disappears and task manager shows that it is just eating up memory
(over 600MB after 10 minutes, and climbing).
Oh sorry, you said 1M polys. That's way too much for OpenOffice. Sorry
for misleading...
Well, back to my first idea: Move into a spatialite DB?
Here's a page of the "Spatialite Cookbook" that explains exactly how to
SELECT a set of features into a new table, and export the selected
features back to a new shapefile.
http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/impexp.html
Briefly:
# Create a new DB:
spatialite erf.sqlite
# You are now in the sqlite interface. To load a shapefile:
> .loadshp /mnt/geo_lvm0/SGO_cadastral/200909/WC/Erf erf LATIN1
# *You'll have to set the correct EPSG code as the SRID above for the
shapefile,
# and choose the appropriate encoding.
*
# Create new table for the selected polys
*> CREATE TABLE zerf (*
*> PK_UID INTEGER PRIMARY KEY AUTOINCREMENT,*
*# add all the other columns you need from the original*
*> "DigitKey21" Text,*
*> );*
*# Now make it spatial
> SELECT AddGeometryColumn('zerf', 'Geometry', , 'MULTIPOLYGON',
'XY');*
*
# And Insert SELECTed rows into the new table
> INSERT INTO zerf (DigitKey21, ..., ..., Geometry)
> (SELECT 21DigitKey, ...,..., Geometry) FROM erf
> WHERE 21DigitKey LIKE 'CO16%';
# Finally export to shapefile
> .dumpshp zerf Geometry zerf_filtered LATIN1
#Should create a shapefile "zerf_filtered.shp" in current directory
HTH,
Micha
*
Thanks,
Z
Greetings,
André Joost
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user
--
Micha Silver
GIS Consultant, Arava Development Co.
http://www.surfaces.co.il
--
===
Zoltan Szecsei PrGISc [PGP0031]
Geograph (Pty) Ltd.
P.O. Box 7, Muizenberg 7950, South Africa.
65 Main Road, Muizenberg 7945
Western Cape, South Africa.
34° 6'16.35"S 18°28'5.62"E
Tel: +27-21-7884897 Mobile: +27-83-6004028
Fax: +27-86-6115323www.geograph.co.za
===
This mail was received via Mail-SeCure System.
--
Micha Silver
GIS Consultant, Arava Development Co.
http://www.surfaces.co.il
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Selecting polygons on attribute - Trying OGR
On 2012/07/24 18:18, Andre Joost wrote: Am 24.07.2012 18:07, schrieb Zoltan Szecsei: Use the Table Manager plugin. (or just open the dbf in OpenOffice and change *only* the name of the column) Which is the Table Manager plugin? I'm just browsing QGIS plugins repository and cannot see anything like this. Its a third party plugin: http://bwj.aster.net.pl/qgis-oldapi/index.html http://hub.qgis.org/wiki/17/Deleting_a_column_from_an_attribute_table I'm afraid they have not moved it to the official repository, as advised for 1.8.0. So you have to download it amnually from here: http://bwj.aster.net.pl/qgis/plugins.xml unzip it and put it in your .qgis/python/plugin folder. OK done, but tomorrow I will work out if I can use it in my Win7 install of Lisboa. Thanks thus far and good night - gotta run . Z I tried with OpenOffice 3.3 on both 64 & 32 bit Win 7, but the job disappears and task manager shows that it is just eating up memory (over 600MB after 10 minutes, and climbing). Ok, for such a huge database, OpenOffice is not the right tool. Greetings, André Joost ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user -- === Zoltan Szecsei PrGISc [PGP0031] Geograph (Pty) Ltd. P.O. Box 7, Muizenberg 7950, South Africa. 65 Main Road, Muizenberg 7945 Western Cape, South Africa. 34° 6'16.35"S 18°28'5.62"E Tel: +27-21-7884897 Mobile: +27-83-6004028 Fax: +27-86-6115323 www.geograph.co.za === ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Selecting polygons on attribute - Trying OGR
Am 24.07.2012 18:07, schrieb Zoltan Szecsei: Use the Table Manager plugin. (or just open the dbf in OpenOffice and change *only* the name of the column) Which is the Table Manager plugin? I'm just browsing QGIS plugins repository and cannot see anything like this. Its a third party plugin: http://bwj.aster.net.pl/qgis-oldapi/index.html http://hub.qgis.org/wiki/17/Deleting_a_column_from_an_attribute_table I'm afraid they have not moved it to the official repository, as advised for 1.8.0. So you have to download it amnually from here: http://bwj.aster.net.pl/qgis/plugins.xml unzip it and put it in your .qgis/python/plugin folder. I tried with OpenOffice 3.3 on both 64 & 32 bit Win 7, but the job disappears and task manager shows that it is just eating up memory (over 600MB after 10 minutes, and climbing). Ok, for such a huge database, OpenOffice is not the right tool. Greetings, André Joost ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Selecting polygons on attribute - Trying OGR
On 2012/07/24 17:39, Micha Silver wrote: On 07/24/2012 06:34 PM, Zoltan Szecsei wrote: On 2012/07/24 17:29, Andre Joost wrote: Am 24.07.2012 17:21, schrieb Micha Silver: I think the problem is the attribute column name begins with a digit. Look what happens when I rename my column from above to 21Name: In the Qgis query form, no error is produced, but all elements are 'found' after renaming the field name to another beginning with numbers. Do you mean all 1.1m, or my subset of 545663 records that start with C016? Can't find anywhere in QGis to change the fields name :-( Use the Table Manager plugin. (or just open the dbf in OpenOffice and change *only* the name of the column) Which is the Table Manager plugin? I'm just browsing QGIS plugins repository and cannot see anything like this. I tried with OpenOffice 3.3 on both 64 & 32 bit Win 7, but the job disappears and task manager shows that it is just eating up memory (over 600MB after 10 minutes, and climbing). Thanks, Z Greetings, Andre' Joost ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user -- Micha Silver GIS Consultant, Arava Development Co. http://www.surfaces.co.il -- === Zoltan Szecsei PrGISc [PGP0031] Geograph (Pty) Ltd. P.O. Box 7, Muizenberg 7950, South Africa. 65 Main Road, Muizenberg 7945 Western Cape, South Africa. 34° 6'16.35"S 18°28'5.62"E Tel: +27-21-7884897 Mobile: +27-83-6004028 Fax: +27-86-6115323 www.geograph.co.za === ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Selecting polygons on attribute - Trying OGR
On 07/24/2012 06:34 PM, Zoltan Szecsei wrote: On 2012/07/24 17:29, Andre Joost wrote: Am 24.07.2012 17:21, schrieb Micha Silver: I think the problem is the attribute column name begins with a digit. Look what happens when I rename my column from above to 21Name: In the Qgis query form, no error is produced, but all elements are 'found' after renaming the field name to another beginning with numbers. Do you mean all 1.1m, or my subset of 545663 records that start with C016? Can't find anywhere in QGis to change the fields name :-( Use the Table Manager plugin. (or just open the dbf in OpenOffice and change *only* the name of the column) Greetings, André Joost ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user -- Micha Silver GIS Consultant, Arava Development Co. http://www.surfaces.co.il ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Selecting polygons on attribute - Trying OGR
On 2012/07/24 17:29, Andre Joost wrote: Am 24.07.2012 17:21, schrieb Micha Silver: I think the problem is the attribute column name begins with a digit. Look what happens when I rename my column from above to 21Name: In the Qgis query form, no error is produced, but all elements are 'found' after renaming the field name to another beginning with numbers. Do you mean all 1.1m, or my subset of 545663 records that start with C016? Can't find anywhere in QGis to change the fields name :-( Greetings, André Joost ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user -- === Zoltan Szecsei PrGISc [PGP0031] Geograph (Pty) Ltd. P.O. Box 7, Muizenberg 7950, South Africa. 65 Main Road, Muizenberg 7945 Western Cape, South Africa. 34° 6'16.35"S 18°28'5.62"E Tel: +27-21-7884897 Mobile: +27-83-6004028 Fax: +27-86-6115323 www.geograph.co.za === ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Selecting polygons on attribute - Trying OGR
Am 24.07.2012 17:21, schrieb Micha Silver: I think the problem is the attribute column name begins with a digit. Look what happens when I rename my column from above to 21Name: In the Qgis query form, no error is produced, but all elements are 'found' after renaming the field name to another beginning with numbers. Greetings, André Joost ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Selecting polygons on attribute - Trying OGR
Am 24.07.2012 17:11, schrieb Zoltan Szecsei: Still no: zls@gs0:~$ ogr2ogr -f "ESRI Shapefile" -where " 21DigitKey LIKE 'C016%'" zerf.shp /mnt/geo_lvm0/SGO_cadastral/200909/WC/Erf.shp ERROR 1: SQL Expression Parsing Error: syntax error FAILURE: SetAttributeFilter( 21DigitKey LIKE 'C016%') failed. zls@gs0:~$ Do you habve gdal 1.8.0 or later running on the Linux machine? SQL seems to be expanded since 1.7.x: http://www.gdal.org/ogr/ogr_sql.html Greetings, André Joost ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Selecting polygons on attribute - Trying OGR
On 07/24/2012 06:11 PM, Zoltan Szecsei wrote: On 2012/07/24 17:07, Micha Silver wrote: On 07/24/2012 05:52 PM, Zoltan Szecsei wrote: On 2012/07/24 16:41, Micha Silver wrote: On 07/24/2012 03:10 PM, Zoltan Szecsei wrote: Hi, I have a SHP file of some 1.1m polygons, of which I need to select about half of them, and save them as a separate SHP file. The trouble is when I select either via a Query, or via the TABLE using the select options below, it just falls asleep (100% CPU (ok, 12% as it must be single-threaded)). After 45 minutes or so, I just kick it out and weep. My platform is Win 7 Prof 64 bit (also tried on 32 bit) and using 1.8.0 Lisboa. The selects I have tried are: "sq21code" = "C016*" and also tried using "IN" Any ideas what I am doing wrong and what I should try next? Still no: zls@gs0:~$ ogr2ogr -f "ESRI Shapefile" -where " 21DigitKey LIKE 'C016%'" zerf.shp /mnt/geo_lvm0/SGO_cadastral/200909/WC/Erf.shp ERROR 1: SQL _expression_ Parsing Error: syntax error FAILURE: SetAttributeFilter( 21DigitKey LIKE 'C016%') failed. zls@gs0:~$ 21igitKey BTW is "String" 60 I think the problem is the attribute column name begins with a digit. Look what happens when I rename my column from above to 21Name: [micha@MS-SL6 bike_rides]$ ogr2ogr -f "ESRI Shapefile" -where "21Name LIKE 'Rest%'" waypoints4.shp waypoints.shp ERROR 1: SQL _expression_ Parsing Error: syntax error Can you rename the column to DigitKey21? Thanks & regards, Zoltan -- Micha Silver GIS Consultant, Arava Development Co. http://www.surfaces.co.il -- === Zoltan Szecsei PrGISc [PGP0031] Geograph (Pty) Ltd. P.O. Box 7, Muizenberg 7950, South Africa. 65 Main Road, Muizenberg 7945 Western Cape, South Africa. 34° 6'16.35"S 18°28'5.62"E Tel: +27-21-7884897 Mobile: +27-83-6004028 Fax: +27-86-6115323 www.geograph.co.za === This mail was received via Mail-SeCure System. -- Micha Silver GIS Consultant, Arava Development Co. http://www.surfaces.co.il -- === Zoltan Szecsei PrGISc [PGP0031] Geograph (Pty) Ltd. P.O. Box 7, Muizenberg 7950, South Africa. 65 Main Road, Muizenberg 7945 Western Cape, South Africa. 34° 6'16.35"S 18°28'5.62"E Tel: +27-21-7884897 Mobile: +27-83-6004028 Fax: +27-86-6115323 www.geograph.co.za === This mail was received via Mail-SeCure System. -- Micha Silver GIS Consultant, Arava Development Co. http://www.surfaces.co.il ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Selecting polygons on attribute - Trying OGR
Am 24.07.2012 17:07, schrieb Micha Silver: This worked for me (on a *very* small shapefile. Some of the "Name" attributes where "rest 1", "rest 2" etc): ogr2ogr -f "ESRI Shapefile" -where "Name LIKE 'Rest%'" waypoints3.shp waypoints.shp Using Windows 7 cmd box, the % will get misinterpreted. But this works for me: ogr2ogr -where "name LIKE '2200%%'" test.shp source.shp HTH, André Joost ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Selecting polygons on attribute - Trying OGR
On 2012/07/24 17:07, Micha Silver wrote: On 07/24/2012 05:52 PM, Zoltan Szecsei wrote: On 2012/07/24 16:41, Micha Silver wrote: On 07/24/2012 03:10 PM, Zoltan Szecsei wrote: Hi, I have a SHP file of some 1.1m polygons, of which I need to select about half of them, and save them as a separate SHP file. The trouble is when I select either via a Query, or via the TABLE using the select options below, it just falls asleep (100% CPU (ok, 12% as it must be single-threaded)). After 45 minutes or so, I just kick it out and weep. My platform is Win 7 Prof 64 bit (also tried on 32 bit) and using 1.8.0 Lisboa. The selects I have tried are: "sq21code" = "C016*" and also tried using "IN" Any ideas what I am doing wrong and what I should try next? In addition to Andre's correction about the * wildcard, with 1M polygons, you probably should move the shapefile into a database (i.e spatialite, PostGIS) That would be a bit sad (in terms of extra steps). I'm trying with ogr on Linux, but haven't fiddled the correct syntax yet: zls@gs0:~$ ogr2ogr -where " 21DigitKey LIKE 'C016%' " zerf /mnt/geo_lvm0/SGO_cadastral/200909/WC/Erf.shp ERROR 1: SQL Expression Parsing Error: syntax error FAILURE: SetAttributeFilter( 21DigitKey LIKE 'C016%' ) failed. zls@gs0:~$ This worked for me (on a *very* small shapefile. Some of the "Name" attributes where "rest 1", "rest 2" etc): ogr2ogr -f "ESRI Shapefile" -where "Name LIKE 'Rest%'" waypoints3.shp waypoints.shp Still no: zls@gs0:~$ ogr2ogr -f "ESRI Shapefile" -where " 21DigitKey LIKE 'C016%'" zerf.shp /mnt/geo_lvm0/SGO_cadastral/200909/WC/Erf.shp ERROR 1: SQL Expression Parsing Error: syntax error FAILURE: SetAttributeFilter( 21DigitKey LIKE 'C016%') failed. zls@gs0:~$ 21igitKey BTW is "String" 60 Thanks & regards, Zoltan -- Micha Silver GIS Consultant, Arava Development Co. http://www.surfaces.co.il -- === Zoltan Szecsei PrGISc [PGP0031] Geograph (Pty) Ltd. P.O. Box 7, Muizenberg 7950, South Africa. 65 Main Road, Muizenberg 7945 Western Cape, South Africa. 34° 6'16.35"S 18°28'5.62"E Tel: +27-21-7884897 Mobile: +27-83-6004028 Fax: +27-86-6115323www.geograph.co.za === This mail was received via Mail-SeCure System. -- Micha Silver GIS Consultant, Arava Development Co. http://www.surfaces.co.il -- === Zoltan Szecsei PrGISc [PGP0031] Geograph (Pty) Ltd. P.O. Box 7, Muizenberg 7950, South Africa. 65 Main Road, Muizenberg 7945 Western Cape, South Africa. 34° 6'16.35"S 18°28'5.62"E Tel: +27-21-7884897 Mobile: +27-83-6004028 Fax: +27-86-6115323 www.geograph.co.za === ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Selecting polygons on attribute - Trying OGR
On 07/24/2012 05:52 PM, Zoltan Szecsei wrote: On 2012/07/24 16:41, Micha Silver wrote: On 07/24/2012 03:10 PM, Zoltan Szecsei wrote: Hi, I have a SHP file of some 1.1m polygons, of which I need to select about half of them, and save them as a separate SHP file. The trouble is when I select either via a Query, or via the TABLE using the select options below, it just falls asleep (100% CPU (ok, 12% as it must be single-threaded)). After 45 minutes or so, I just kick it out and weep. My platform is Win 7 Prof 64 bit (also tried on 32 bit) and using 1.8.0 Lisboa. The selects I have tried are: "sq21code" = "C016*" and also tried using "IN" Any ideas what I am doing wrong and what I should try next? In addition to Andre's correction about the * wildcard, with 1M polygons, you probably should move the shapefile into a database (i.e spatialite, PostGIS) That would be a bit sad (in terms of extra steps). I'm trying with ogr on Linux, but haven't fiddled the correct syntax yet: zls@gs0:~$ ogr2ogr -where " 21DigitKey LIKE 'C016%' " zerf /mnt/geo_lvm0/SGO_cadastral/200909/WC/Erf.shp ERROR 1: SQL _expression_ Parsing Error: syntax error FAILURE: SetAttributeFilter( 21DigitKey LIKE 'C016%' ) failed. zls@gs0:~$ This worked for me (on a *very* small shapefile. Some of the "Name" attributes where "rest 1", "rest 2" etc): ogr2ogr -f "ESRI Shapefile" -where "Name LIKE 'Rest%'" waypoints3.shp waypoints.shp Thanks & regards, Zoltan -- Micha Silver GIS Consultant, Arava Development Co. http://www.surfaces.co.il -- === Zoltan Szecsei PrGISc [PGP0031] Geograph (Pty) Ltd. P.O. Box 7, Muizenberg 7950, South Africa. 65 Main Road, Muizenberg 7945 Western Cape, South Africa. 34° 6'16.35"S 18°28'5.62"E Tel: +27-21-7884897 Mobile: +27-83-6004028 Fax: +27-86-6115323 www.geograph.co.za === This mail was received via Mail-SeCure System. -- Micha Silver GIS Consultant, Arava Development Co. http://www.surfaces.co.il ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Selecting polygons on attribute - Trying OGR
On 2012/07/24 16:41, Micha Silver wrote: On 07/24/2012 03:10 PM, Zoltan Szecsei wrote: Hi, I have a SHP file of some 1.1m polygons, of which I need to select about half of them, and save them as a separate SHP file. The trouble is when I select either via a Query, or via the TABLE using the select options below, it just falls asleep (100% CPU (ok, 12% as it must be single-threaded)). After 45 minutes or so, I just kick it out and weep. My platform is Win 7 Prof 64 bit (also tried on 32 bit) and using 1.8.0 Lisboa. The selects I have tried are: "sq21code" = "C016*" and also tried using "IN" Any ideas what I am doing wrong and what I should try next? In addition to Andre's correction about the * wildcard, with 1M polygons, you probably should move the shapefile into a database (i.e spatialite, PostGIS) That would be a bit sad (in terms of extra steps). I'm trying with ogr on Linux, but haven't fiddled the correct syntax yet: zls@gs0:~$ ogr2ogr -where " 21DigitKey LIKE 'C016%' " zerf /mnt/geo_lvm0/SGO_cadastral/200909/WC/Erf.shp ERROR 1: SQL Expression Parsing Error: syntax error FAILURE: SetAttributeFilter( 21DigitKey LIKE 'C016%' ) failed. zls@gs0:~$ Thanks & regards, Zoltan -- Micha Silver GIS Consultant, Arava Development Co. http://www.surfaces.co.il -- === Zoltan Szecsei PrGISc [PGP0031] Geograph (Pty) Ltd. P.O. Box 7, Muizenberg 7950, South Africa. 65 Main Road, Muizenberg 7945 Western Cape, South Africa. 34° 6'16.35"S 18°28'5.62"E Tel: +27-21-7884897 Mobile: +27-83-6004028 Fax: +27-86-6115323 www.geograph.co.za === ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
