Re: [postgis-users] Fwd: Hello everyone, That is my first message.I am using PostGIS not so long and so I ask you to help me.
This query from http://postgis.refractions.net/pipermail/postgis-users/2009-April/023265.html should give you an idea of how to do this. The radius is an arbitrary value to avoid a full table scan. If you already know which feature is closest & just want the closest vertex from that feature, add a where clause to include just that feature. HTH Brent Wood > select * from vertices_tmp where > st_expand(setsrid(makepoint(x,y),4326),radius) && the_geom > order by distance(setsrid(makepoint(x,y),4326),the_geom) asc > limit 1; --- On Sun, 5/22/11, Александр Дымчишин wrote: From: Александр Дымчишин Subject: [postgis-users] Fwd: Hello everyone, That is my first message.I am using PostGIS not so long and so I ask you to help me. To: postgis-users@postgis.refractions.net Date: Sunday, May 22, 2011, 8:35 AM I'm trying to solve the transport problem and make the visualization using Geotools. In attached pic that is what I mean. My car is between V3 and V4. Its position can I get with a query like "SELECT ST_Line_Interpolate_Point(linestring1, 0.6);" Ok, I got it's position, but how can I receive nearest real vertices v3 and v4? I hope, there is a method with only one query. Thanks. -Inline Attachment Follows- ___ 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] [postgis-devel] how to keep geometry_columns in sync wit tablesand views (and new PostGIS 2.0 plans)
* Paragon Corporation (l...@pcorp.us) wrote: > I would almost go with forcing everyone to change their existing tables to > typmod if they want to reap the benefits of PostGIS 2.0, > but most of my clients will not go to 2.0 then. What I havn't heard yet is any explanation or description of *why* they wouldn't want to move to 2.0 if it only supported the typmod approach..? > We could try Mark's idea of hacking the catalog tables to automagically > convert table constraints to typmod but that all sounds pretty scary to me. The upgrade question should not be terribly difficult to solve by just using the current geometry_columns table to help with the generation of the new tables...? > Especially with my special > Inheritcance case -- I can just see that failing miserably and screwing up > my tables. Can you provide more insight on this..? I'm not familiar with your 'special inheritance case'.. > Anyrate we can't try any of these until you put in place the typmod feature. > Once you put in place typmod -- I think we can exercise all the various > options to see which evil is the least of all evils. Part of the issue here, however, is if there's something the typmod approach isn't doing currently that it could/should be doing which would resolve these concerns...? Thanks, Stephen signature.asc Description: Digital signature ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] [postgis-devel] how to keep geometry_columns in sync wit tablesand views (and new PostGIS 2.0 plans)
Steve, * Paragon Corporation (l...@pcorp.us) wrote: > I would almost go with forcing everyone to change their existing > tables to typmod if they want to reap the benefits of PostGIS 2.0, but > most of my clients will not go to 2.0 then. > What I havn't heard yet is any explanation or description of *why* they wouldn't want to move to 2.0 if it only supported the typmod approach..? See my comment in the trac item - (short answer there are some use cases that just can't be implemented with typmod) http://trac.osgeo.org/postgis/ticket/944#comment:22 > We could try Mark's idea of hacking the catalog tables to > automagically convert table constraints to typmod but that all sounds pretty scary to me. > The upgrade question should not be terribly difficult to solve by just using the current geometry_columns table to help with the generation of the new tables...? New tables -- oh no, you'd have to do an alter (or as Mark wants directly update the catalogs) -- imagine the nightmare if you have foreign key constraints and what not having to create new tables. I have no tables to test with since we don't have typmod, but once Paul gets typmod in there (HINT HINT), I can test with fairly large tables to see how painful doing an alter and dropping constraints would be. > Especially with my special > Inheritcance case -- I can just see that failing miserably and > screwing up my tables. > Can you provide more insight on this..? I'm not familiar with your 'special inheritance case'.. See trac comment. Thanks, Regina http://www.postgis.us ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ogr dxf
Here is the main resource: http://www.gdal.org/ogr/drv_dxf.html And Frank's blog regarding the driver: http://fwarmerdam.blogspot.com/search/label/dxf -Mike On 20 May 2011 06:13, Bob Pawley wrote: > Hi > > Has anyone used OGR to convert DXF to Postgis and Postgis to DXF? > > Perhaps, you could point me to a site that will help me. So far I haven’t > been able to find much information. > > Bob > ___ > 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] [Solved] Linemerge roads
FTR here is my adopted solution to labeling a roading layer. Its long winded but works. As im just learning id welcome any improvements/suggestions. Nb: the unique road 'id' in the dataset is called road_name_, and there are between 1 and lots of records per id, as the road surface changes or has oddities like small Ys, or Ps. -- remove all those roads with no names to r2 create table r2 as select gid,road_name_,name,road_highw,the_geom from nzroads where name is null and road_name_ is null -- load the rest to r3 create table r3 as select gid,road_name_,name,road_highw,road_surfa,the_geom from nzroads where not ( name is null and road_name_ is null) -- take out oddities to r4 (20 or so) create table r4 as select gid,road_name_,name,road_highw,road_surfa,the_geom from r3 where name is null or road_name_ is null or road_name_ <'1' or road_name_ ~* '[a-z]' delete from r3 where name is null or road_name_ is null or road_name_ <'1' or road_name_ ~* '[a-z]' -- multipart the multilinestrings from r3 create table r5 as select row_number() over (order by road_name_)::int AS gid, count(gid) as nlines, road_name_, (st_collect(the_geom)) as the_geom from r3 group by road_name_ -- then linemerge them so they can be labelled as one create table r6 as select row_number() over (order by road_name_)::int AS gid, road_name_, (st_linemerge(the_geom)) as the_geom from r5 -- but first separate the resulting single and multis to r7 and r8 -- to save cpu on the much larger set of simple cases create table r8 as select row_number() over (order by road_name_)::int AS gid, road_name_, the_geom from r6 where GeometryType("the_geom") = 'MULTILINESTRING' create table r7 as select row_number() over (order by road_name_)::int AS gid, road_name_, the_geom from r6 where GeometryType("the_geom") = 'LINESTRING' -- the multis are the trickiest -- buffer to 1m, and merge by id create table r9 as select row_number() over (order by road_name_)::int AS gid, road_name_, ST_Buffer(the_geom, 1) as the_geom from r8 -- then re-multipart so we can label the sep parts indepedantly create table r10 as select row_number() over (order by road_name_)::int AS gid, road_name_, (st_dump(the_geom)).geom as the_geom from r9 -- label the resultant polys using pointonsurface, a close enough approximation to line mid point create table r11 as select row_number() over (order by road_name_)::int AS gid, road_name_, st_pointonsurface(the_geom) as the_geom from r10 -- label the easier single linestring midpoints create table r12 as select row_number() over (order by road_name_)::int AS gid, road_name_, ST_Line_Interpolate_Point(the_geom,0.5) as the_geom from r7 -- merge both sets of points, r11 and r12 create table r13 as SELECT * FROM r11 UNION ALL SELECT * FROM r12 -- join road names back in from r3 -- is there a way to do this join in one step? create table r14 as select distinct on (road_name_) * from r3 order by road_name_ create table r15 as SELECT r13.gid, r14.name, r14.road_name_, r14.road_highw, r14.road_surfa, r13.the_geom FROM r13 LEFT JOIN r14 ON r14.road_name_ = r13.road_name_ order by r13.road_name_ Peter ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users