This from the ogr2ogr documentation[1] may be relevant: "When writing into transactional DBMS (SQLite/PostgreSQL,MySQL, etc...), it might be beneficial to increase the number of INSERT statements executed between BEGIN TRANSACTION and COMMIT TRANSACTION statements. This number is specified with the -gt option. For example, for SQLite, explicitly defining *-gt 65536* ensures optimal performance"
Mike [1] http://www.gdal.org/ogr2ogr.html On Tue, Mar 22, 2016 at 5:01 PM, Stefan Keller <sfkel...@gmail.com> wrote: > Hi Frederik and Jukka > > Before I try give answers to performance let's be aware that we're (at > least I am) speaking about a "desktop exchange format", not a storage > fomat for GIS processing. > > But Frederik's comment piqued my curiosity and I did some quick comparison. > I generated 1 mio. records in PostsGIS with this table > CREATE TABLE benchmark (id serial primary key, txt varchar(32), geom > geometry(point,4326) ); > > Then I used OGR2OGR to create the following three file formats: > GeoPackage (using 73.9 MB disk space), Shapefiles (dbf/shp/shx 117 MB) > and Spatialite (173 MB). > > Creation time of GeoPackage was 18 sec., Shapefile 21 sec. and > Spatialite 1 min 51 sec. > So, GeoPackage is a bit faster than Shapefiles and significantly > (about 37%) smaller in size. > Spatialite in fact consumes much more disk space than Shapefile and > GeoPackage, and Spatialite is several times slower for creation time. > > This could explain the preformance issues of Spatialite Frederic mentioned. > > :Stefan > > 2016-03-22 13:56 GMT+01:00 Jukka Rahkonen <jukka.rahko...@latuviitta.fi>: > > Frederik Ramm <frederik <at> remote.org> writes: > > > >> > >> Hi, > >> > >> On 03/20/2016 10:56 PM, Stefan Keller wrote: > >> > But Shapefile remains an oldtimer with more drawbacks than limited > >> > field names; see [1]. > >> > GeoJSON (ascii) and GeoPackages (binary) are formats which are more > >> > suited for the job. > >> > I still have hope that JOSM will be able to read those vector formats > too. > >> > >> Frankly, whenever I venture into the brave new world of Spatialite, I > >> come back to good old shape files after a while for performance reasons. > >> I'm not sure if Geopackage has significant performance improvements over > >> simple Spatialite but if it hasn't then my recommendation for simple GIS > >> processing is certainly to stick with shape files for the time being - > >> despite all their shortcomings. > > > > > > Hi Frederic, > > > > I would like to receive some sample data, exact way to reproduce some of > > your ventures and cold numbers about the speed you have experienced. > > Spatialite does have it's limits but for plain selects with spatial and > > attribute filters it can well outperform both shapefiles and PostGIS. > > > > I keep most vector data for WMS services in Spatialite or GeoPackage due > to > > the already mentioned and some other reasons: > > - supports long attribute names > > - supports strings longer than 255 characters > > - supports SQL > > - supports attribute indexes > > - much less encoding problems due to UTF-8 > > - one single file vs. a bunch of files in shapefile, perhaps even split > to > > separate bunches for points, lines and polygons. > > > > For me SpatiaLite is a little bit slower than shapefiles if only spatial > > filter (BBOX) is used but usually faster if also attribute filters are > > involved, especially if more than one field is needed in filters > (Shapefiles > > can be sorted by one attribute only). Of course spatialite must have > indexes > > which suit the queries and when it comes to spatial index, the client > must > > know how to utilize the table based R-Tree index. I also recommend to > VACUUM > > once the database is ready to use. > > > > Many spatial operations are relatively slow in Spatialite and I don't > > usually utilize them on-the-fly with WMS server. Instead, I run the > > algorithm once and store the result into a new table because a few > > mega/gigabytes of additional disk space is not crucial on the server. > > However, such operations tend to be slow also if shapefiles are used as > > source data. > > > > Write performance especially with concurrent writes is another story. I > am > > talking about read-only operations. I know that I am writing empty words > as > > far as I do not include reproducible facts but I am willing to join to a > > controlled test if someone is organizing such. > > > > -Jukka Rahkonen- > > > > > > > > > > _______________________________________________ > > talk mailing list > > talk@openstreetmap.org > > https://lists.openstreetmap.org/listinfo/talk > > _______________________________________________ > talk mailing list > talk@openstreetmap.org > https://lists.openstreetmap.org/listinfo/talk >
_______________________________________________ talk mailing list talk@openstreetmap.org https://lists.openstreetmap.org/listinfo/talk
