[postgis-users] How to change srid
dear, iv loaded data to postgis and for all geometry srid is -1. if i want to change srid without doing and transformations, can i do that and how? Thank you, Marko Cubranic ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] reverse geocoding
On 30/03/2010, Kevin Neufeld wrote: > On 3/29/2010 9:58 PM, Stephen Woodbridge wrote: >> If you have a POINT then you can find the closest centerline within >> some RADIUS with: >> >> select *, distance(POINT, the_geom) as dist >> from lines >> where expand(POINT, RADIUS) && the_geom >> order by dist limit 1; >> >> -Steve > > Or to find the closest centerline for all points in your point table: > > SELECT DISTINCT ON (a.gid) a.*, b.*, ST_Distance(a.geom, b.geom) AS dist > FROM points a, lines b > WHERE ST_Expand(a.geom, RADIUS) && b.geom > ORDER BY a.gid, dist ASC; I've found some data sets have very long road linestrings, and this makes the spatial index less effective, so if this is the case, you want to create a new table of roads (for search purposes only) and break the linestrings up into smaller linestrings. I used a plpgsql function that did a select on the entire roads line table, and row by row did this: calculate the length of the linestring using length2d do simple maths to work out how many segments I wanted then in a for loop, used line_substring to create the segments. Insert each segment into a new table, copying across the UID of the original linsestring Then of course, create a spatial index on this new (larger) table. To do the reverse geocoding, I then search using a SQL very similar to Kevin's, and also search on a points table (points of interest such as Church, Petrol Station etc) Then also on the polygons tables to get the suburb etc. The hardest part of reverse geocoding is that you need to become very fmiliar with your data set, and set up the parameters of your search so that the results are sensible. Sometimes the data set is not so good, and you need to make a lot of tweaks to work around this. > > Cheers, > Kevin > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > -- Brian Modra Land line: +27 23 5411 462 Mobile: +27 79 69 77 082 5 Jan Louw Str, Prince Albert, 6930 Postal: P.O. Box 2, Prince Albert 6930 South Africa http://www.zwartberg.com/ ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] reverse geocoding
On 3/29/2010 9:58 PM, Stephen Woodbridge wrote: If you have a POINT then you can find the closest centerline within some RADIUS with: select *, distance(POINT, the_geom) as dist from lines where expand(POINT, RADIUS) && the_geom order by dist limit 1; -Steve Or to find the closest centerline for all points in your point table: SELECT DISTINCT ON (a.gid) a.*, b.*, ST_Distance(a.geom, b.geom) AS dist FROM points a, lines b WHERE ST_Expand(a.geom, RADIUS) && b.geom ORDER BY a.gid, dist ASC; Cheers, Kevin ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] reverse geocoding
Richard Greenwood wrote: I'm not even sure if "reverse geocoding" is the correct term, but what I am trying to do is improve TIGER road centerline data from known points. I have a set a accurate address points, and I would like to assign fromleft, toleft, fromright, toright ranges to the TIGER centerlines from these points. I am just doing this for one small community, so I don't intend to turn it into a big programming project. i.e. I don't want to spend more time doing it programatically than it would take to do it "by hand". But if there is a tool or some code out there that would help, I sure would appreciate any pointers. Thanks, Rich If you have a POINT then you can find the closest centerline within some RADIUS with: select *, distance(POINT, the_geom) as dist from lines where expand(POINT, RADIUS) && the_geom order by dist limit 1; -Steve ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Nested loop join = very bad performance
Mark/List, I just replaced my postgresql.conf with the default copy that appears in /etc/postgresql/8.4/main/ after a fresh install. The performance is pretty much the same as before (maybe even about 400 ms worse than before). Is there anything else I should try? Mike On Monday 29 March 2010 17:35:29 Mike Leahy wrote: > Hi Mark, > > I don't recall making any changes...at least not until the error I > encountered last week cropped up - but I think I reversed those changes (I > will double- check tonight). But I think I have encountered performance > problems with this query before on other systems - I had originally > dismissed it as hardware limitations I was dealing in my testing system at > the time. After recent hardware upgrades and after dealing with that > specific bug, that's when the slow performance of this query really stood > out. > > Mike > > On Monday 29 March 2010 07:53:50 Mark Cave-Ayland wrote: > > Mike Leahy wrote: > > > Is this something that should be looked into, or should I just > > > incorporate the workaround of disabling nested loop joins into my code? > > > > I find that generally PostgreSQL is fairly good at its estimates unless > > someone has already tried to tune the database. Have you changed any of > > the settings in postgresql.conf from their defaults? > > > > > > ATB, > > > > Mark. > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] reverse geocoding
I'm not even sure if "reverse geocoding" is the correct term, but what I am trying to do is improve TIGER road centerline data from known points. I have a set a accurate address points, and I would like to assign fromleft, toleft, fromright, toright ranges to the TIGER centerlines from these points. I am just doing this for one small community, so I don't intend to turn it into a big programming project. i.e. I don't want to spend more time doing it programatically than it would take to do it "by hand". But if there is a tool or some code out there that would help, I sure would appreciate any pointers. Thanks, Rich -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Address Geocoding
When you write: "FROM (roads, settlements, poi, province, etc...)" you
actually mean one table at a time? Because if you try more than one table
separated by commas, there will be an error. If you are trying to use more
than one table I'm afraid you will need to use a JOIN clause ...
Regards,
On Wed, Mar 24, 2010 at 6:21 AM, ibrahim saricicek <
ibrahimsarici...@gmail.com> wrote:
> Hi;
>
> for reverse geoceding now using this and works fine;
>
> SELECT id, the_geom
> FROM (roads, settlements, poi, province, etc...)
> WHERE
> the_geom && SetSRID('BOX3D(x+0.01 y+0.01, x-0.01 y-0.01)'::box3d, 4326)
> ORDER BY
> ST_Distance(the_geom, GeomFromText('POINT(x y)', 4326))
> LIMIT 1;
>
> regards...
>
>
> On Fri, Mar 19, 2010 at 8:23 PM, Stephen Woodbridge <
> wood...@swoodbridge.com> wrote:
>
>> http://www.google.com/#hl=en&source=hp&q=postgis+geocoder
>>
>> Ricardo Bayley wrote:
>>
>>> Hi fellows,
>>>
>>> I was wondering if any of you have a good approach to do Address
>>> Geocoding within postgis.
>>> Any ideas, considerations, thoughts ??
>>>
>>> thanks in advanced.
>>>
>>>
>>> Warm regards,
>>>
>>>
>>> Ricardo
>>>
>>>
>>>
>>>
>>>
>>> ___
>>> 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
>>
>
>
> ___
> 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] Nested loop join = very bad performance
Hi Mark, I don't recall making any changes...at least not until the error I encountered last week cropped up - but I think I reversed those changes (I will double- check tonight). But I think I have encountered performance problems with this query before on other systems - I had originally dismissed it as hardware limitations I was dealing in my testing system at the time. After recent hardware upgrades and after dealing with that specific bug, that's when the slow performance of this query really stood out. Mike On Monday 29 March 2010 07:53:50 Mark Cave-Ayland wrote: > Mike Leahy wrote: > > Is this something that should be looked into, or should I just > > incorporate the workaround of disabling nested loop joins into my code? > > I find that generally PostgreSQL is fairly good at its estimates unless > someone has already tried to tune the database. Have you changed any of > the settings in postgresql.conf from their defaults? > > > ATB, > > Mark. > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS online
On Mar 28, 2010, at 6:29 PM, Nicklas Avén wrote: Hallo everybody PostGIS online is a new site for trying, testing and showing PostGIS. You can try sql queries against the test datasets (so far only one) and follow tutorials (so far only two) or you can write tutorials yourself to let other know how to do nifty things, an instruction how to do that will soon be found in the document area of the site. I have written a few words about it here http://blog.jordogskog.no/2010/03/28/postgis-online/ Welcome Nicklas Avén http://blog.jordogskog.no Now I understand what Sarah Palin meant when she said that she could see Russia from her porch. She only saw the Republican elephant :-) ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] shp2pgsql in quite mode????...(shp2pgsql -q)???
Hello, Indeed there is no -q. But are you sure that it actually is shp2pgsql that you want to quiet down ? (there are at most 15 lines) I think that what can annoy you is the output from the psql that usually is piped behind shp2pgsql. Psql do have a -q parameter. e.g. : shp2pgsql -W utf-8 -s 4326 -d -I /path/to/some/shp my_table | psql -q -d my_database -h host -U user Thats the command I used most of the time. If you really want to remove shp2pgsql output I think that your only option is using something like command > null to redirect the ouput nowhere. Hope this helps. Nicolas De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de Shreerang Patwardhan Envoyé : lundi 29 mars 2010 12:44 À : Pune GNU/Linux Users Group Mailing List; PostGIS Users Discussion Objet : [postgis-users] shp2pgsql in quite mode...(shp2pgsql -q)??? Hey all, I do not wish to display the output of the command shp2pgsql at the terminal. I want the command to run in quite mode. But there is no -q option for shp2pgsql. How do I get around this issue? Thanx, Shreerang Patwardhan. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] shp2pgsql in quite mode????...(shp2pgsql -q)???
Shreerang Patwardhan wrote: Hey all, I do not wish to display the output of the command shp2pgsql at the terminal. I want the command to run in quite mode. But there is no -q option for shp2pgsql. How do I get around this issue? Perhaps something like: shp2pgsql ... > /dev/null or shp2pgsql ... 2>&1 > /dev/null HTH, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Nested loop join = very bad performance
Mike Leahy wrote: Is this something that should be looked into, or should I just incorporate the workaround of disabling nested loop joins into my code? I find that generally PostgreSQL is fairly good at its estimates unless someone has already tried to tune the database. Have you changed any of the settings in postgresql.conf from their defaults? ATB, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] shp2pgsql in quite mode????...(shp2pgsql -q)???
Hey all, I do not wish to display the output of the command shp2pgsql at the terminal. I want the command to run in quite mode. But there is no -q option for shp2pgsql. How do I get around this issue? Thanx, Shreerang Patwardhan. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PSQL command to drop table from a databae
Hey Kevin, Thanx a million tons.My problem is solved...Thanx a lot.!!! ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
