Hi ,
Can somebody suggest me how to convert a polygon to line.
Thank You
-Nicholas I
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
I don't know about combining indexes but you want to include a spatial
operator such as in addition to Distance so that you can take advantage
of an index on the_geom.
-Original Message-
From: postgis-users-boun...@postgis.refractions.net
Thanks :)
Does PostGIS support partial indices?
http://www.postgresql.org/docs/8.3/interactive/indexes-partial.html
If so.. I could do something like this:
CREATE INDEX building_type_spindx_school
ON buildings
USING GIST(the_geom)
WHERE building_type='School';
CREATE INDEX
Well according to EXPLAIN a partial index is taken into account, how much of
a performance benefit you gain ultimately depends on how many records lie
within your average distance.
If your data set is relatively static you may also want to consider
clustering your table too.
-Original
Suppose I want to speedup the following type of queries with an index:
SELECT the_geom, building_name
FROM buildings
WHERE Distance(the_geom, 'POINT(100312 102312)') 5000
AND building_type = 'School'
Is that possible, and if not, why not? My current option is to write
my own index
ST_Boundary
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of
Nicholas I
Sent: Wednesday, March 18, 2009 2:44 AM
To: postgis-users@postgis.refractions.net
Subject: [postgis-users] polygon to
Thanks, that's true. I think the performance benefit with be good
because I have approx 10.000 building_type's (although, that's a lot
of indices to choose between). A typical distance query for a specific
building_type will result in 10-100 matches. Not using partial
indexing will thus
For what you want to do I don't think partial indexs are the the solution as
you would have too many indicies. You don't want the query taking more time
going through the metadata then the actual query.
Postgres will use a separate index you have on building type, infact if you
look at EXPLAIN
Hi,
I have a byte array that I would like to att to a table as a blob.
I have not succeded.
I managed to ad it as an bytea, but it is really slow. Could some one please
help me?
I'm using Java to populate the database on a windows XP OS.
Kind regards,
Paul
I am having much trouble getting postgres to use the proper index.
I have a table with about 20 million daily rainfall observation points,
with between 0 and 19,000 points on any given day. In order to speed up
querying, I have created a GIST index on the geometry column, and an
index on the
lr,
Thanks for your response.
The dimensions of the problem are relevantly different, something like
this:
* number of building_types: 10.000
* number of buildings: 5 billion
* average number rows returned by the query (function of distance,
building type): 100
(the problems something other
I thinks you're right about the 'too many indices' , I'm worried about
that too. I'll have to do some test's now to see what would work.
Thanks!
..anyway, one thing I've learned is that there are no compound spatial/
non-spatial indices possibilities.
On Mar 18, 2009, at 1:43 PM, Kemal
Well spatial indicies are not actually accurate because they use 32bit
floating point and work on bounding boxes, that is one assumption I
mistakenly made myself (should have RTFM properly :) So it makes sense that
you can not have a compound index.
-Original Message-
From:
Also thinking about your problem a little bit more, you could divide your
space into sub divisions giving each space a unique id. If you then assign
each building the unique id of its correspending sub division you can create
a compound index on building_type and sub_division_id. For your query
That's what I'm doing right now! (so far the update has been running
20 minutes)
Basically this is replicating the spatial index idea with classic
database operators.
Yet another possibility is to move things in 3D (and use the cube
index) having, x,y the building location, and uze the
Robert
I don't know how far back your data goes but you might look at this:
http://www.postgresql.org/docs/8.2/interactive/ddl-partitioning.html
Another application similar to yours used this inherited tables idea to
partition by months and a query for rainfall 15 years ago is now the
same as
It's possible to create GIST indexes on multiple columns of mixed types.
Here's an example of an index I use on my data:
create index listing_search_keys_centroid on listing_search_keys USING
GIST (centroid, listing_price, num_bedrooms) where centroid is not null;
This application is used for
that's exactly what I want to do!
In my case I would have centroid and and integer (the integer
representing the building_type and being constrained on equality)
Do you have some more reference/info? Where did you learn this?
On Mar 18, 2009, at 4:24 PM, Michael Smedberg wrote:
It's
Info on GIST indexes is available here:
http://www.postgresql.org/docs/8.2/static/gist.html
Some info on PostGIS and GIST is available here:
http://postgis.refractions.net/documentation/manual-1.3/ (specifically
http://postgis.refractions.net/documentation/manual-1.3/ch04.html#id2743
709)
Thanks
I'm going to try this multi column index approach, see how it performs!
Apart from the question in the osdir post, none of those links
mentions compound indices though. I did a lot of searching myself, but
it's very hard to find any example of a compound spatial/non-spatial
index.
The GEOS team is pleased to announce that GEOS 3.1.0 has been pushed
out the door, cold, wet, trembling and looking for love.
http://download.osgeo.org/geos/geos-3.1.0.tar.bz2
Version 3.1.0 includes a number of improvements over the 3.0 version:
- PreparedGeometry operations for very fast
Bruce,
Thanks, this is certainly relevant to my case -- I tried to CLUSTER the
database on date to achieve some economics, but this seems to be an even
more explicit way to achieve this. I will experiment with this for
certain.
Still, though, it seems as if the index is not functioning
Robert,
Which version of PostgreSQL are you running? I wander if you are being
bitten by one of these bugs
--the description doesn't quite match any of these 2 - but it could be
another side effect of these
http://www.postgresql.org/docs/8.3/static/release-8-3-7.html
Fix planner problem with
Robert, if you could EXPLAIN ANALYZE your bad query, that would point
out where the mis-estimation is occurring. It sounds much like the
planner thinks the spatial index is more selective than it is.
Also, the advice below on partitioning is good, for any service based
on a large continuous data
Regina,
Thanks, I am using 8.2, and you may be right. I need to migrate to 8.3,
but am going on vacation in 3 days, and don't want to kill the
production box - it is our only box currently...
Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia Department of
Robert,
I have been haunted by similar issues but I haven't had time to figure out
some explanation I really believe in.
Does this post with a reproducible case seem similar? It seems like it to
me.
http://postgis.refractions.net/pipermail/postgis-devel/2008-September/003547.html
For now when
Robert,
I think you just need to replace your binaries. The same fix was put in the
8.2 release so if you are running lower than 8.2.12, might help to upgrade.
http://www.postgresql.org/docs/8.2/static/release-8-2-12.html
Fix planner misestimation of selectivity when transitive equality is
27 matches
Mail list logo
