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
