Tag Info

Hot answers tagged

44

Different approach. Knowing that the pain is in ST_Intersection, and that true/false tests are fast, trying to minimize the amount of geometry passing through the intersection might speed things up. For example, parcels that are totally contained in a jurisdiction don't need to be clipped, but ST_Intersection will still probably go to the trouble of building ...


11

For displaying purposes it is always good to use a spatial index. It will improve speed of both rendering and spatial queries. However, if you plan to update large quantities of objects, it might be wise to remove the spatial index during the update. Otherwise the update process will become significantly slower, because with every update the spatial index ...


8

This is an exciting question! How big is the raster you want to query? WKTRaster is stored in the database as a BLOB. In order to find the value at a specific point, from a known (x_0, y_0) corner coordinate row/column indices (i, j) are computed using (dx, dy) steps and rotation. With (i, j) known, the ST_Value() function can access the actual data at the ...


6

Have you tried disabling the spatial index first? SELECT DisableSpatialIndex('TableName', 'geom'); DROP TABLE idx_tablename_Geometry; -- check to see if it is now gone .tables -- if it is gone, vacuum to clean up your db VACUUM;


6

Here are two non-Spatialite solutions. For Rtree with Python, try this example with 13000 points -- should take a few seconds: from random import randrange from rtree import index from math import sqrt # Create a 3D index p = index.Property() p.dimension = 3 idx3d = index.Index(properties=p) # Make and index random data coords = [] for id in ...


6

There's two things going on here: the GIST API in PostgreSQL and the bindings of types to that API for the purposes of building an R-Tree. PostGIS necessarily uses the PostgreSQL GIST API. That's what it's for. That way we don't have to worry about transaction management or writing things to disk or all the other messy important things involved in ...


5

If you want to batch create indexes on geometry columns, you could try this plpgsql function I've just knocked up: CREATE OR REPLACE FUNCTION BatchIndex(sn text, tn text, cn text) RETURNS void AS $$ DECLARE i_exists integer; DECLARE idxname text; BEGIN idxname := 'idx_' || tn || '_' || cn; select into i_exists count(*) from pg_class where relname = ...


5

Try running the sp_help_spatial_geography_index stored procedure to get details on how your spatial index is being used. You should be able to use something like: declare @ms_at geography = 'POINT (-95.66 30.04)' set @ms_at = @ms_at.STBuffer(1000).STAsText() exec sp_help_spatial_geography_index 'lidar', 'SPATIAL_lidar', 0, @ms_at; Post the results in ...


5

One method would be to query for the tags you are interested in and place those records in a new table. Then you will only need to query the new table instead of all 53 million records. If you are trying to keep your database updated, you could have this query run every time you get new data from OSM.


5

On large databases or a database with may changes it can be very important to have spatial indexes in place and updated regularly. (Keeping it simple here) For example for Oracle Spatial indexing capabilities into the Oracle database engine is a key feature of the Spatial product. A spatial index, like any other index, provides a mechanism to limit ...


4

Something must be wrong with your mysql installation or the .ini settings. Just tested a geospatial index on my old mac (10.6.8 / MySQL 5.2). That configuration is similar to yours and I tested the big geodata dump (9 million records). I did this query: SET @radius = 30; SET @center = GeomFromText('POINT(51.51359 7.465425)'); SET @r = @radius/69.1; SET ...


4

It would depend on the spatial distribution. I would recommend quad-tree only for a somewhat uniform data set such as parcels for an inner city region. If you have concentrations of small features in certain areas I would go with a more dynamic tree structure such as an r-tree. There are even cases where depending on the data it may be better to not index ...


4

Effectively forcing the planner to do the thing you want might help. In this case, sub-setting the polygon table prior to executing the spatial join with the points table. You might be able to outwit the planner using "WITH" syntax: WITH polys AS ( SELECT * FROM area WHERE area.id in ...


4

I would not use a conventional point->polygon process because that expects your points to define the boundary of a polygon and it doesn't sound like yours do. It sounds like yours are hotspots that are somehow related. However, there are lots of ways to create polygons for this sort of situation depending on what is sensitive in your data. Here's a few ...


4

An index is used to filter a query using the "WHERE" part of the statement. A GiST index is not used by ST_Union. Without a "WHERE" part, then no filtering (or index) is required to return the result, and the query just chugs through all the rows in the query. As described in the manual, not all functions make use of indicies, for example ST_Distance and ...


4

Yes. It does as it appears from looking at the source code for the Spatialite Data Provider. The QgsSpatiaLiteFeatureIterator class is the one that supplies the features to the map upon sending a rectangle extent. You can just search for 'spatialIndex' in that class to see they actually use the index if available.


3

A standard multicolumn b-tree index on the two columns is probably the most effective solution provided that: both columns are used together in the where expression. A multicolumn index will not be used when only the second column is present A multicolumn index is more efficient than two indexes on both columns because it will use less storage and will ...


3

What would happen if you omit the "st_multi(st_intersection(a.geom,b.geom))" part? Doesn't the below query mean the same thing without it? I ran it on the data you provided. INSERT INTO parcel_jurisdictions(parcel_gid,jurisdiction_gid,isect_geom) SELECT a.orig_gid parcel_gid, b.orig_gid jurisdiction_gid, a.geom FROM valid_parcels a, ...


3

Maybe try googling for specific indexing methods. For example, when I Google for C# r-tree this is the first result.


3

I don't believe there is any shorter way of writing this query. Yes, you're right, there does appear to be redundancy on first sight, however I believe the idea is that the index subquery is evaluated first by the query optimiser. This then computes the subset of records where the query could possibly be true. This is a very quick operation because it's ...


3

MySQL, like PostGIS, stores it’s spatial index data in an R-tree structure so it can find stuff fast. An R-tree, like a B-tree, is organized in such a manner that it is optimized for retrieving only a small fraction of the total data in the table. It is actually faster to ignore the index for queries that need to read a large section of ...


3

The st_within and _st_within functions are not known for their speed. The && operator might help as it will check bbox instead of geometry You might try the following: SELECT n.geom,n.tags,n.tstamp,u.name FROM nodes AS n INNER JOIN users AS u ON n.user_id = u.id WHERE tags ? 'man_made' AND tags->'man_made'='surveillance' AND geom ...


3

You can try to create an index for your hstore column, CREATE INDEX nodes_tags_idx ON nodes USING GIST(tags) and then use the ? operator to limit the query to only that rows: SELECT n.geom,n.tags,n.tstamp,u.name FROM nodes AS n INNER JOIN users AS u ON n.user_id = u.id WHERE tags ? 'man_made' AND tags->'man_made'='surveillance' AND ...


3

Do you have a cut-off distance for the how far you wish to search for the closest feature? If so, you might consider using ISpatialCacheManager3 instead. I've heard people have had problems with IFeatureIndex2. Once you've called FillCache, you should be able to quickly return features with IFeatureClass.Search using a small search envelope whose width ...


3

You're correct that your problem is that in order to sort, then limit to the closest, you have to generate the distance to each of the 100k points, which is extremely time consuming. Luckily, there's help. First, you want to make sure that your geometry column is indexed, if it's not, then you need to index it, or this won't work. Then you want to use the ...


3

Your questions are answered in Rtree's docs, which come with the source and are online at http://toblerity.github.com/rtree/tutorial.html. You can make your shapefile indexing code much simpler: for i, shape in enumerate(shapes): idx.insert(i, shape.bbox) Use Python's enumerate() function for this kind of thing whenever possible. It's tidy and ...


3

Depending on the distribution of your data, you might get some very good speedups just by indexing the date_of_data column. You can use the EXPLAIN ANALYZE syntax to figure out if your indexes are being used or not.


3

Assuming you have a spatialindex created already on the polygon layer 'blayer', then your query would be: UPDATE strlayer SET fromBland =( SELECT name FROM blayer AS n, strlayer AS s WHERE strlayer.strid = s.strid AND n.blevel = 4 AND within(startpoint(s.geometry),n.geometry) AND s.ROWID IN ( SELECT ROWID FROM SpatialIndex WHERE ...


3

Spatial fragmentation can make a random spatial distribution of data perform poorly, no matter how you tune the spatial index. Try duplicating the table by exporting the features by county id or some other attribute that clusters the data (or if you have to, by a systematic search grid that partitions the data by at least in ten tiles in each dimension), ...


3

As seen in the comments, it was a bug. You can see the bug in the bugtracker. It was fixed in the 2.1.2 release However, being caught by that bug outlined a fundamental issue in our design, and made it possible to realize that the app would not be performing well using such a generic type. I would not advise to use GeometryCollections unless you know ...



Only top voted, non community-wiki answers of a minimum length are eligible