Re: [postgis-users] Creating points along a line for every second?
Hi Robert, Thanks for your suggestion - this worked great - and was easier than learning how to do loops and similar! (though thanks for the suggestions from others too!). I knew just a bit of lateral thinking was required! James On 17 August 2012 20:48, Robert Burgholzer wrote: > If i understand this right, the series field tells you which second in total > time of N seconds that it took to move along the line. (end_time - > start_time)::float should give you the total time in seconds, and > series/total_time should give you the parameter that you want for > st_line_interpolate_point > > HTH > r.b. > On Friday, August 17, 2012, James David Smith wrote: >> >> Hi everyone, >> >> I'm flirted around this problem for a while, so I hope I'm not bein >> annoying by being repetative, but I've been tearing my hair out trying to do >> it. The problem is that I have a file of lines (geometry). in a table called >> temp4 The format of the table is, simplified, >> >> line >> start_time >> end_time >> series (I generated this as a count of the seconds difference between the >> above time fields. I thought it would be useful) >> >> So for example my table (temp4) might look like this: >> >> line||start_time || end_time|| >> series >> ~~ >> line_geom || 12:07:17|| 12:07:20 || >> 3 >> line_geom || 12:07:17|| 12:07:20 || >> 2 >> line_geom || 12:07:17|| 12:07:20 || >> 1 >> >> line_geom || 12:07:21|| 12:07:26 || >> 6 >> line_geom || 12:07:21|| 12:07:26 || >> 5 >> line_geom || 12:07:21|| 12:07:26 || >> 4 >> line_geom || 12:07:21|| 12:07:26 || >> 3 >> line_geom || 12:07:21|| 12:07:26 || >> 2 >> line_geom || 12:07:21|| 12:07:26 || >> 1 >> >> >> Now what I want to do is split the lines into points, with a point for >> each second. Evenly spaced along the line. So if the line is 10 metres long >> and the difference between the start and end is 10 seconds then I want to >> create 9 points at 10% along the line, 20% along the line, 30% along the >> line etc up to a point which is 100% along the line i.e. the end of the >> line. I don't need a new point at the start of the line as I have that >> stored already. >> >> So I've been trying something like this, but it's wrong. But maybe along >> the right lines. >> >> SELECT st_line_interpolate_point(line, 1/series::float)) as new_point >> FROM temp4 >> >> Taking the first few rows of my data, the percentages along the lines I >> want to generate are as follows. I need to find a way to get these values >> into the st_line_interpolate_line function. >> >> line || start_time || end_time || >> series || percentage along line >> ~~ >> line_geom|| 12:07:17 || 12:07:20 || 3 >> || 0.33% >> line_geom|| 12:07:17 || 12:07:20 || 2 >> || 0.67 >> line_geom|| 12:07:17 || 12:07:20 || 1 >> || 100% >> >> Sorry for the long explanation. Hope that you understand where I am coming >> from. Grateful for any ideas please! >> >> Thanks >> >> James > > > > -- > -- > Robert W. Burgholzer > http://www.findingfreestyle.com/ > On Facebook - > http://www.facebook.com/pages/Finding-Freestyle/151918511505970 > Twitter - http://www.twitter.com/findfreestyle > What's a tweeted swim set? A Sweet? No, a #swaiku! Get them by following > http://twitter.com/findfreestyle > > ___ > 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] Creating points along a line for every second?
Hi everyone, I'm flirted around this problem for a while, so I hope I'm not bein annoying by being repetative, but I've been tearing my hair out trying to do it. The problem is that I have a file of lines (geometry). in a table called temp4 The format of the table is, simplified, line start_time end_time series (I generated this as a count of the seconds difference between the above time fields. I thought it would be useful) So for example my table (temp4) might look like this: line||start_time || end_time ||series ~~ line_geom || 12:07:17|| 12:07:20 ||3 line_geom || 12:07:17|| 12:07:20 ||2 line_geom || 12:07:17|| 12:07:20 ||1 line_geom || 12:07:21|| 12:07:26 ||6 line_geom || 12:07:21|| 12:07:26 ||5 line_geom || 12:07:21|| 12:07:26 ||4 line_geom || 12:07:21|| 12:07:26 ||3 line_geom || 12:07:21|| 12:07:26 ||2 line_geom || 12:07:21|| 12:07:26 ||1 Now what I want to do is split the lines into points, with a point for each second. Evenly spaced along the line. So if the line is 10 metres long and the difference between the start and end is 10 seconds then I want to create 9 points at 10% along the line, 20% along the line, 30% along the line etc up to a point which is 100% along the line i.e. the end of the line. I don't need a new point at the start of the line as I have that stored already. So I've been trying something like this, but it's wrong. But maybe along the right lines. SELECT st_line_interpolate_point(line, 1/series::float)) as new_point FROM temp4 Taking the first few rows of my data, the percentages along the lines I want to generate are as follows. I need to find a way to get these values into the st_line_interpolate_line function. line || start_time || end_time || series || percentage along line ~~ line_geom|| 12:07:17 || 12:07:20 || 3 || 0.33% line_geom|| 12:07:17 || 12:07:20 || 2 || 0.67 line_geom|| 12:07:17 || 12:07:20 || 1 || 100% Sorry for the long explanation. Hope that you understand where I am coming from. Grateful for any ideas please! Thanks James ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS to Web Map?
Thank you sounds like Geoserver might be first on my list to try:-) On 17 August 2011 17:58, muhammad imran wrote: > James, Geoserver is good option for the beginner (windows user), but > presently it is less powerful as compared to the UMN mapserver. Geosever > could also generate the output in the Openlayers format (some basic > functionality). But, if you need some extra interactivity (client side > functionality) with the maps, you have to enrich the generated output with > extra coding ... > > Imran > > --- On Wed, 8/17/11, James David Smith wrote: > >> From: James David Smith >> Subject: Re: [postgis-users] PostGIS to Web Map? >> To: "PostGIS Users Discussion" >> Date: Wednesday, August 17, 2011, 10:09 PM >> Afternoon, >> >> Thanks for all of your help on this area. For information I >> already do >> use QGIS, but wanted to experiment with ways to display >> the >> information from my database on a webpage/webmap. So that >> a) I can >> change what is displayed on the map using different queries >> and b) as >> data changes in my database, the data being shown on the >> map should >> change. >> >> Given what you have all said, it seems like I should try >> one of QGIS >> Server, Geoserver or OpenGeo. Which one would be the >> simplest do you >> think? >> >> Also, could someone clarify the flow for me? It is >> PostGIS > (one of >> above) > Webpage? Where does OpenLayers come in, >> or doesn't it!? >> >> THanks again everyone. James. >> >> >> >> On 17 August 2011 16:40, Andreas Neumann >> wrote: >> > To explore the data I would recommend QGIS (Quantum >> GIS). >> > >> > To publish the data you can either use: >> > * QGIS server >> > * Geoserver >> > * UMN Mapserver >> > >> > QGIS server has the advantage that publishing an >> existing QGIS desktop >> > project is fast and easy. However, it only publishes >> WMS (not WFS). >> > Geoserver has the advantage that it is not only a WMS >> server, but also WFS >> > and data export/import. UMN is probably the fastest of >> all three, if speed >> > is an issue, but more complex to configure. >> > >> > Andreas >> > >> > On Wed, 17 Aug 2011 08:36:48 -0700 (PDT), muhammad >> imran wrote: >> >> >> >> Agreed with Bruce, Geoeserver or Openlayers are >> not merely to explore >> >> the data stored in PostGIS. Rather, it is about >> exploring a theme that >> >> is interested for a certain audience. Therefore, a >> logical flow is, >> >> conceptualize a theme, conceptualize users, >> accordingly design >> >> database and construct conceptual schema, >> transform your data >> >> following the conceptual schema (could be only one >> table), construct >> >> geoserver layers, and finally enrich the >> visualization and >> >> interactivity through GPOS (Openlayers, google >> maps, ). Shortly, >> >> monstrous is still in the data. >> >> >> >> Imran >> >> >> >> --- On Wed, 8/17/11, b...@brucecallander.com >> >> >> wrote: >> >> >> >>> From: b...@brucecallander.com >> >> >>> Subject: Re: [postgis-users] PostGIS to Web >> Map? >> >>> To: "PostGIS Users Discussion" >> >>> Date: Wednesday, August 17, 2011, 8:18 PM >> >>> James - My guess is that the >> >>> straightforward answer is to use Geoserver or >> similar. >> >>> Writing the software to extract data from >> PostGIS and turn >> >>> it into a web service seems like unnecessary >> pain. >> >>> >> >>> I have come at this from the opposite point of >> view. I >> >>> wanted to learn more about how WMS, WFS, WCS >> worked and how >> >>> different http request formats could be tested >> so installed >> >>> Geoserver. But I then needed sample data in >> the background >> >>> on which Geoserver could operate so downloaded >> PostGIS. >> >>> Getting that up and running is turning out to >> be a bigger >> >>> challenge than installing Geoserver. >> >>> >> >>> Bruce Callander >> >>> >> >>> >> >>> >> >>> On 16 Aug 2011, at
Re: [postgis-users] PostGIS to Web Map?
Afternoon, Thanks for all of your help on this area. For information I already do use QGIS, but wanted to experiment with ways to display the information from my database on a webpage/webmap. So that a) I can change what is displayed on the map using different queries and b) as data changes in my database, the data being shown on the map should change. Given what you have all said, it seems like I should try one of QGIS Server, Geoserver or OpenGeo. Which one would be the simplest do you think? Also, could someone clarify the flow for me? It is PostGIS > (one of above) > Webpage? Where does OpenLayers come in, or doesn't it!? THanks again everyone. James. On 17 August 2011 16:40, Andreas Neumann wrote: > To explore the data I would recommend QGIS (Quantum GIS). > > To publish the data you can either use: > * QGIS server > * Geoserver > * UMN Mapserver > > QGIS server has the advantage that publishing an existing QGIS desktop > project is fast and easy. However, it only publishes WMS (not WFS). > Geoserver has the advantage that it is not only a WMS server, but also WFS > and data export/import. UMN is probably the fastest of all three, if speed > is an issue, but more complex to configure. > > Andreas > > On Wed, 17 Aug 2011 08:36:48 -0700 (PDT), muhammad imran wrote: >> >> Agreed with Bruce, Geoeserver or Openlayers are not merely to explore >> the data stored in PostGIS. Rather, it is about exploring a theme that >> is interested for a certain audience. Therefore, a logical flow is, >> conceptualize a theme, conceptualize users, accordingly design >> database and construct conceptual schema, transform your data >> following the conceptual schema (could be only one table), construct >> geoserver layers, and finally enrich the visualization and >> interactivity through GPOS (Openlayers, google maps, ). Shortly, >> monstrous is still in the data. >> >> Imran >> >> --- On Wed, 8/17/11, b...@brucecallander.com >> wrote: >> >>> From: b...@brucecallander.com >>> Subject: Re: [postgis-users] PostGIS to Web Map? >>> To: "PostGIS Users Discussion" >>> Date: Wednesday, August 17, 2011, 8:18 PM >>> James - My guess is that the >>> straightforward answer is to use Geoserver or similar. >>> Writing the software to extract data from PostGIS and turn >>> it into a web service seems like unnecessary pain. >>> >>> I have come at this from the opposite point of view. I >>> wanted to learn more about how WMS, WFS, WCS worked and how >>> different http request formats could be tested so installed >>> Geoserver. But I then needed sample data in the background >>> on which Geoserver could operate so downloaded PostGIS. >>> Getting that up and running is turning out to be a bigger >>> challenge than installing Geoserver. >>> >>> Bruce Callander >>> >>> >>> >>> On 16 Aug 2011, at 19:11, James David Smith wrote: >>> >>> > Dear all, >>> > >>> > Bit of a geneal question this rather than something >>> specific, but I've >>> > been following a few of the other queries on here >>> talking about Open >>> > Layers, Geoserver and Google Maps, and it's reminded >>> me that I really >>> > want to try and learn how to connect/convert my local >>> PostGIS database >>> > into something that renders on a webpage. I realise >>> that this is a big >>> > question, but wondered if anyone could give me a basic >>> few steps as >>> > way of pointers - and then perhaps some links to more >>> detail where I >>> > can go and learn how to do each step myself? I'm very >>> happy to learn - >>> > just not sure where to start! >>> > >>> > To be clear, at the moment I have a local database on >>> my machine. I >>> > guess really I need to move this database to an online >>> environment and >>> > host it somewhere? Then I need to build some webpages >>> with a map, that >>> > interact with the database? There's no specific >>> project here, just >>> > something I want to learn in my spare time as it'll be >>> useful for the >>> > future. >>> > >>> > As you can see my knowledge of how to put all this >>> together is a bit >>> > vague to say the least. If it helps I have basic >>> experience of Java + >>> > Javescript including the Google Maps API, am decen
[postgis-users] PostGIS to Web Map?
Dear all, Bit of a geneal question this rather than something specific, but I've been following a few of the other queries on here talking about Open Layers, Geoserver and Google Maps, and it's reminded me that I really want to try and learn how to connect/convert my local PostGIS database into something that renders on a webpage. I realise that this is a big question, but wondered if anyone could give me a basic few steps as way of pointers - and then perhaps some links to more detail where I can go and learn how to do each step myself? I'm very happy to learn - just not sure where to start! To be clear, at the moment I have a local database on my machine. I guess really I need to move this database to an online environment and host it somewhere? Then I need to build some webpages with a map, that interact with the database? There's no specific project here, just something I want to learn in my spare time as it'll be useful for the future. As you can see my knowledge of how to put all this together is a bit vague to say the least. If it helps I have basic experience of Java + Javescript including the Google Maps API, am decent at HTML and CSS, and am becoming familiar with PostgreSQL and PostGIS - I just don't know how to join them all up at the moment - or where the gaps in my knowledge are. Best wishes James ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] spatial indices on two geometry or rast columns
Hey Vishal,
I guess really you should just re-project on the fly so that you don't
have the same data in two columns, but like you I have created another
column in the project I'm working on at the moment for convenience. I
did it like this:
SELECT AddGeometryColumn ('wards', 'geom_utm','32643','POINT','2');
UPDATE wards
set "geom_utm" = (SELECT ST_transform("geom", 32643));
I'm not sure about the GIST question or the raster thing I'm afraid.
Hope this helps.
Cheers
James
On 11 August 2011 20:16, Vishal Mehta wrote:
> Hi all,
>
>
>
> I have some vector layers in a postgis database in EPSG 4326. Spatial
> indices have been built using GIST on the same (EPSG 4326) geometries on a
> geometry column called geom.
>
>
>
> I’d like to transform these layers to UTM (EPSG 32643). I was thinking of
> creating a new geometry column for each table and
>
> ALTER TABLE wards ADD column geom_utm geometry;
>
> UPDATE wards SET geom_utm = ST_TRANSFORM(geom,32643);
>
>
>
> Should I, (and can I) create another gist index on the transformed geometry?
>
>
>
> The same qn applies on a raster table I have called srtm_table – should I
> and can I add
>
> - a new ‘rast’ column called rast_utm with transformed SRID in
> similar fashion above, and then add a gist index on the rast_utm column?
>
>
>
> CREATE INDEX “srtm_table_rast_gist_idx” ON “public”.”srtm_table” USING GIST
> (st_convexhull(rast_utm));
>
>
>
> Thanks,
>
> Vishal
>
>
>
> Vishal K. Mehta, Ph.D.
>
> Staff Scientist,
>
> Stockholm Environment Institute-US
>
> 400 F St, Davis, CA 95616
>
> http://sei-us.org/
>
>
>
>
>
>
>
> ___
> 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] Mapserver with postgis, sql query error.
Hey Nicholas,
I'm a newbie, but just to say that I've used the 'Spit' tool within
QGIS to load shapefiles into PostGIS - mght be worth a look. You can
also then load the table into QGIS once it is in the system to see if
it has stored it correctly?
James
On 30 July 2011 15:17, Nicolas ( cse )
wrote:
> Hi,
> I'm a newby with this.
> I have a shape file that I upload to postGis Database, with the windows
> program "postgis and dbf loader".
> Then I create a test with MS4W environment.
> I setup a LOG in the map file.
> And the error y following:
>
> [Sat Jul 30 01:45:34 2011].75 CGI Request 1 on process 4620
> [Sat Jul 30 01:45:34 2011].75 msDrawMap(): WMS/WFS set-up and query,
> 0.000s
> [Sat Jul 30 01:45:34 2011].906000 msPostGISLayerWhichShapes(): Query error.
> Error (ERROR: geometry requires more points
> ) executing query: select
> encode(AsBinary(force_collection(force_2d("the_geom")),'NDR'),'hex') as
> geom,"gid" from t_parcelas where the_geom &&
> GeomFromText('POLYGON((5600615.254754 5725111.70807103,5600615.254754
> 5739818.70784397,5620240.983548 5739818.70784397,5620240.983548
> 5725111.70807103,5600615.254754
> 5725111.70807103))',find_srid('','t_parcelas','the_geom'))
> [Sat Jul 30 01:45:34 2011].906000 msDrawMap(): Image handling error. Failed
> to draw layer named 'Parcelas'.
> [Sat Jul 30 01:45:34 2011].906000 msFreeMap(): freeing map at 01DBAD80.
>
>
> This is the layer section in the map file
>
> ...
> LAYER # States polygon layer begins here
> NAME Parcelas
> CONNECTIONTYPE postgis
> CONNECTION "user=postgres password=postgres dbname=muni_gis"
> DATA "the_geom from t_parcelas"
> STATUS DEFAULT
> TYPE POLYGON
> PROJECTION
> "init=epsg:4221"
> END
> END # States polygon layer ends here
>
> The table is populated, everything looks fine, but I can't see any map.
>
> Then I enter the SQl Query in the SQL Window of pgAdmin III, and start to
> rewrite this sql query:
> select encode(AsBinary(force_collection(force_2d("the_geom")),'NDR'),'hex')
> as geom,"gid" from t_parcelas where the_geom &&
> GeomFromText('POLYGON((5600615.254754 5725111.70807103,5600615.254754
> 5739818.70784397,5620240.983548 5739818.70784397,5620240.983548
> 5725111.70807103,5600615.254754
> 5725111.70807103))',find_srid('','t_parcelas','the_geom'))
> With thi error:
> ERROR: geometry requires more points
> ** Error **
> ERROR: geometry requires more points
> SQL state: XX000
>
> If I extract function AsBinary, there is no error:
> Total query runtime: 9625 ms.
> 67354 rows retrieved.
>
> Here is my question:
> 1) How to check if the data is correct ?
> 2) do I have lo load data with shp2pgsql command line utility?
> 3) any idea ?
> Best Regards.
> Nicolas
> --
>
> ___
> .:Nicolás Machado
>
> ___
> 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] ST_area returning wrong results (and stretched polygon in the screen)
Dear pvaldes, Have you tried to do this with type 'geometry' instead of 'geography' ? James On 21 July 2011 13:42, p valdes wrote: > Hi, > I'm trying to calculate the area of a polygon, with strange results... > > First a description of the problem, the ugly details at the end of the post > > I have measured the perimeter of a pond. An irregular > polygon vaguely 'chop shaped' without holes or spikes > and closed (beginning and ending in the same point). > For simplification purposes let's suppose that I was > expecting something like the left shape... > well, I'm obtaining instead this: > > expected obtained > --- --- > | | | | > | | | | > | |__ | | > | | | | > --- | | > | | > | | > | |_ > - > > The right shape is "not right". The polygon is clearly > stretched in the vertical North-South axis in the screen. The > satellite google map support that the real shape of > the pond is the first represented and I think the same... > > Well I could live with this, but there's two more nasty consequences. > > 1) If I take a point inside or related to the polygon > in a separated map layer the point is drawn OUTSIDE > and far away below the figure of the pond (note also that the > pond is printed much more big than expect, because the relative distance > between a and b is the same in both, and seems right). Graphically: > > expected obtained > > --*a --- > | | | | > | | | | > | | | | > | | | | > | | | | > | | | | > | | | | > | |_ | |_ > -*b -- > > > > > > > *a > *b > > Maybe a problem of the map viewer software, yes but the second > question is more serious: > > ST_distance calculates a distance between the "a" and "b" points of > about 160 m for this rectangle. A max length of 160 m for this pond > seems reasonable to me. > > but ST_area calculates an area for this polygon of almost 7 ha!, for a > polygon that could fit in a rectangle of about 160x50 m!. > I'm obtaining a faked area about seven times BIGGER than real, uh-oh... > > Its clear to me that I'm doing a very obvious and stupid mistake. I > greatly appreciated if you could take a look at the problem or suggest > any possible source of the error > > Thanks in advance > > pvaldes. > > > > . > Details > .. > > The points were registered with a garmin GPS as coordinates in degrees > (ie: 43.254 N -4.839 W) > > I'm using PostgreSQL 9.0.4 > on i486-pc-linux-gnu, compiled by GCC gcc-4.6.real (Debian > 4.6.0-6) 4.6.1 20110428 (prerelease), 32-bit > (obtained as Debian package from an official repository) > > postgis-1.5.3 > (downloaded and compiled from the tarfile at postgis.refractions.net) > > This is the table > > CREATE TABLE mytable( > ref serial PRIMARY KEY, > name varchar(80), > perimeter geography(POLYGON,4326) > ); > > The polygon with lat/lon coords was loaded with something like: > > INSERT INTO mytable (perimeter, name) > VALUES ( > ST_GeogFromText( > 'POLYGON(( > -5.3850 43.230, > -5.3858 43.231, > -5.3859 43.245, > ... > -5.3850 43.230 > ))', 4326),'myname'); > > And this is the very simple query returning strange results > > SELECT st_area(mytable.perimeter)::double precision AS square_meters > FROM mytable > WHERE name = 'myname'; > > I use pgsql2shp for obtaining a shapefile that I'm loading with thuban > ___ > 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] Support for LINESTRING and CIRCULARSTRING in the same polygon.
Mats, Maybe the thing to do is to use the below function...? GEOMETRYCOLLECTION(POINT(2 3),LINESTRING((2 3,3 4))) Then inside the Geometry collection, you can put both a Polygon and a Curve Polygon ? This webpage seems to be a good place to read about all the different types of shape you can store... though I guess you've seen it already... http://postgis.refractions.net/docs/ch04.html Cheers James On 20 July 2011 20:27, Mats Taraldsvik wrote: > Hi, > > On 07/20/2011 08:53 PM, James David Smith wrote: >> >> Hey Mats, >> >> I'm just a newbie, so don't really have an answer for you I'm afraid, >> but if a Polygon has a curved line, then it isn't a Polygon I don't >> think. Polygons have only straight lines. >> > > Yes. My internal polygon objects (for a corporate gis system) may contain > both, though, and I need a way to store them in PostGIS. > >> Am quite curious what the answer is to your query though... ! Good luck. >> >> James > > Thanks! :) > > cheers, > Mats > >> On 20 July 2011 19:48, Mats Taraldsvik wrote: >>> >>> Hi, >>> >>> I have to deal with Polygons which have curves as well as straight lines. >>> How should I store them in postgis? (If I understand the documentation >>> correctly, Polygon is for linestrings, CurvePolygon is for >>> circularstrings, >>> but none of them handles a mix of both..?) >>> >>> Regards, >>> Mats Taraldsvik >>> ___ >>> 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] Support for LINESTRING and CIRCULARSTRING in the same polygon.
Hey Mats, I'm just a newbie, so don't really have an answer for you I'm afraid, but if a Polygon has a curved line, then it isn't a Polygon I don't think. Polygons have only straight lines. Am quite curious what the answer is to your query though... ! Good luck. James On 20 July 2011 19:48, Mats Taraldsvik wrote: > Hi, > > I have to deal with Polygons which have curves as well as straight lines. > How should I store them in postgis? (If I understand the documentation > correctly, Polygon is for linestrings, CurvePolygon is for circularstrings, > but none of them handles a mix of both..?) > > Regards, > Mats Taraldsvik > ___ > 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] converting to lat long
YJ,
I'm a beginner, so take with a pinch of salt, but first I'd question
why you want/need to separate the latitude & longitude?
I would keep them together and store them as a point in a new column.
First create a column to store the Lat & Long in with something like
this:
SYNTAX:SELECT AddGeometryColumn(, ,
, , )
EXAMPLE: SELECT AddGeometryColumn('parks', 'park_geom','4326', 'POINT', 2 );
Then you want to populate that column, with the existing data from
your geocode column. Using something like this:
UPDATE tablename
SET latlongcolumn = ST_GeomFromText('geocode', 4326)
I guess that there is a way to pull out the lat and long into seperate
columns, but I don't know how to do that I'm afraid.
Cheers
James
On 12 July 2011 13:58, Yamini Singh wrote:
> Hi All,
>
> I have a column 'geocode' in a table which has attributes like 2329/4727 now
> I would like to convert these attributes in another column to 'lat' and
> 'long'. For example, 'Lat' column will have attribute '23.29' and 'long'
> column will have attribute '47.27'.
>
> Is there a way through which this can be converted automatically by a query
> or so.
>
> Looking fwd..
>
>
>
> Thanks
>
> YJ
>
> ___
> 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] Newbie - Spatial Join with a large dataset
Rich, If you've got those details then you should be good to go. Use the ST_Within Function to select the points that are within the polygons of the contients that you want to show. http://www.postgis.org/docs/ST_Within.html Cheers James On 11 July 2011 18:54, Rich B wrote: > Thank you! > Yes I do have both of those datasets. I'll run the spatial join that way and > see what happens. > *fingers crossed* > > > > On Jul 11, 2011, at 5:20 AM, James David Smith > wrote: > >> Hey Rich, >> >> I'm a newbie myself, but it seems to me that you either need a table >> that defines the geometry of the continents (so that you can do a >> spatial join with the points), or the points table itself needs a >> column which tells you in which continent that point is located. Do >> you have either of these at the moment? >> >> Cheers >> >> James >> >> On 10 July 2011 20:34, Rich B wrote: >>> Hello all, >>> >>> I have a newbie question for you guys…here goes : >>> >>> >>> >>> I have a large dataset (a geonames world data-dump) that I’ve imported into >>> Postgis. >>> >>> I want to know how to do a Spatial Join with this dataset so that I can >>> split the millions of points up into chunks by continent. >>> >>> For example : >>> >>> A user connects to the database in QGIS and wants to select all of the >>> cities (I’m going to trim the data down a little more using a query later >>> on) and they want to bring in only the points that fall in Europe or Africa. >>> >>> In a nutshell I want a user to select all the points by continent instead of >>> loading the whole dataset. >>> >>> >>> >>> I’m sure there’s an easy way to do this , and I’m probably going to slap >>> myself for not thinking of this earlier. >>> >>> I’m relatively new to OSGIS and I’m getting the hang of it. I’ve been an >>> ESRI guy for a while. >>> >>> >>> >>> Thanks in advance for the feedback. >>> >>> >>> >>> Cheers!!! >>> >>> ___ >>> 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] Newbie - Spatial Join with a large dataset
Hey Rich, I'm a newbie myself, but it seems to me that you either need a table that defines the geometry of the continents (so that you can do a spatial join with the points), or the points table itself needs a column which tells you in which continent that point is located. Do you have either of these at the moment? Cheers James On 10 July 2011 20:34, Rich B wrote: > Hello all, > > I have a newbie question for you guys…here goes : > > > > I have a large dataset (a geonames world data-dump) that I’ve imported into > Postgis. > > I want to know how to do a Spatial Join with this dataset so that I can > split the millions of points up into chunks by continent. > > For example : > > A user connects to the database in QGIS and wants to select all of the > cities (I’m going to trim the data down a little more using a query later > on) and they want to bring in only the points that fall in Europe or Africa. > > In a nutshell I want a user to select all the points by continent instead of > loading the whole dataset. > > > > I’m sure there’s an easy way to do this , and I’m probably going to slap > myself for not thinking of this earlier. > > I’m relatively new to OSGIS and I’m getting the hang of it. I’ve been an > ESRI guy for a while. > > > > Thanks in advance for the feedback. > > > > Cheers!!! > > ___ > 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] PG Routing
Dear Maria, Thank you for your reply. I already have PostgreSQL + PostGIS database running which contains all of my information. The data is not a live system, it is data from March 2010. I am doing some research on the data. I have discovere that as I am using PostgreSQL 9.0 I cannot use pgRouting unfortunately. So I now have two questions please? 1) If I uninstall PostgreSQL 9.0, and then install PostgreSQL 8.3 (so I can use pgRouting), will my databases still be intact? Or will I lose all of my tables and data? 2) Maybe an easier solution would be for me to create an OS GEO bootable USB drive, and then do the work in Ubuntu? If I did this, will my existing PostgreSQL databases be accesible through the Ubuntu version of PostgreSQL? http://live.osgeo.org/en/quickstart/usb_quickstart.html Thank you for your help James On 4 July 2011 07:49, Maria Arias de Reyna wrote: > El Domingo 03 Julio 2011, James David Smith escribió: > > Dear all, > > > > I have a table of GPS points which are the locations of vehicles every > > 15 seconds. Each journey that a car makes has an ID. For example a > > taxi is on 'job 1' and has 50 points while it does this job, and then > > another 50 or 60 when it is on 'job 2' etc. What I would like to do > > however is investigate whether the car took the most effecient route > > between the two points. To this end I think that I can use the > > PGROUTING extension of PostGIS...? Does this sound possible? > > You can calculate the shortest path with pgRouting and then compare it with > the length of the linestring formed with this GPS points. Probably you will > have a longer path with the GPS points (because the GPS is never exact) but > if > you add a percent of error to the shortest path calculated by pgRouting you > will know if the vehicle did a good or a bad path. > > > I have > > found the PGROUTING website, and download the ZIP file for Windows > > installation, however I am not sure how to install it. Could somebody > > please tell me how or provide a link to a good place to read about how > > to install and use PGROUTING? > > Why are you using Windows (for any serious work)? There is a PPA for > Ubuntu[1] > which has a direct install while pgRouting binaries for Windows are old and > strongly dependent of a postgresql version. And remember that you must > install > postgis first. > > Anyway, if you are thinking of having a real-time server with several > vehicles > sending positions every 15 seconds, forget Windows. It will be insane. If > it > is insane if you do it on a non-strongly-optimized database on Linux I > don't > want to know how hard it will be on Windows. Believe me, I have made a few > of > this real-time systems. > > > [1]https://launchpad.net/~georepublic/+archive/pgrouting<https://launchpad.net/%7Egeorepublic/+archive/pgrouting> > -- > María Arias de Reyna Domínguez > Área de Operaciones > > Emergya Consultoría > Tfno: +34 954 51 75 77 / +34 607 43 74 27 > Fax: +34 954 51 64 73 > www.emergya.es > ___ > 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] PG Routing
Dear all, I have a table of GPS points which are the locations of vehicles every 15 seconds. Each journey that a car makes has an ID. For example a taxi is on 'job 1' and has 50 points while it does this job, and then another 50 or 60 when it is on 'job 2' etc. What I would like to do however is investigate whether the car took the most effecient route between the two points. To this end I think that I can use the PGROUTING extension of PostGIS...? Does this sound possible? I have found the PGROUTING website, and download the ZIP file for Windows installation, however I am not sure how to install it. Could somebody please tell me how or provide a link to a good place to read about how to install and use PGROUTING? Alternatively, is there a better way of accomplosing my task? Best wishes James ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] UpdateGeometrySRID error
Hi, I'm a novice, but shouldn't it be something like this... ? UPDATE table SET geom = ST_SetSRID(geom, 4326) Cheers James On 16 June 2011 09:23, Andreas Forø Tollefsen wrote: > Hi, > > I have merged 4 tables with line geometry. In total 1028110 rows. > However, when i had unionized the tables, i realized that i had forgot > to set the SRID for 2 of the tables. > Hence, I now have a table where half of the roughly half a million > rows have a SRID. > > I then tried to run the UpdateGeometrySRID, but something is wrong. > Syntax seems correct according to manual: > http://postgis.refractions.net/documentation/manual-svn/UpdateGeometrySRID.html > > > SELECT UpdateGeometrySRID(groads2, geom, 4326); > > > groads2 is my table, geom is the geometry column and the srid should > be 4326 WGS84. > > However, when running the above query, i get this: > > ERROR: column "groads2" does not exist > LINE 3: SELECT UpdateGeometrySRID(groads2, geom, 4326); > ^ > > ** Error ** > > ERROR: column "groads2" does not exist > SQL state: 42703 > Character: 99 > > Any ideas? > ___ > 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] QGIS - PostGIS - ArcGIS Interaction
Hi Andreas, I'm sure that QGIS could do exactly what ArcGIS can do, it's just that I do not have time at the moment to learn how. I am familiar with ArcGIS at the moment, so while I appreciate that is a poor excuse, I'm going to keep using ArcGIS for the time being. Maybe later this year I will learn more QGIS. Best wishes James On 16 June 2011 12:34, Andreas Neumann wrote: > Hi James, > > Glad you found a good solution for your problem. > > Of course we'd like to know what keeps you from using QGIS instead of > ArcGIS? Where would QGIS have to improve that you would consider using it? > > Thanks, > Andreas > > On Thu, 16 Jun 2011 12:23:13 +0100, James David Smith wrote: >> >> Dear all, >> >> Thank you so much for your help. >> >> shp2pgsql - I didn't realise it automaticaly renamed columns. Awesome. >> I'll use that. >> >> ArcGIS 10 - I'm on 9 unfortunately. No access to 10 at the moment. >> >> RT SQL - I glanced at that the other day and they seem to have >> stopped supporting ArcGIS 9, and also you had to pay for anything more >> than 100 rows of data I think? I may have read that incorrectly, but >> the shp2pgsql solution seems good so I'll just go with that anyway. >> >> Cheers all, this list is awesome! >> >> James :-) >> >> >> On 15 June 2011 21:44, David Fawcett wrote: >>> >>> James, >>> >>> There a couple of SQL plugins for QGIS. They allow you to visualize >>> spatial features via 'ad hoc' queries (not whole tables). >>> >>> I am pretty sure that I used the RT_SQL plugin. Here is a link to >>> some info: >>> http://underdark.wordpress.com/2010/10/16/visualizing-postgis-queries-in-qgis-using-rt-sql-layer-plugin/ >>> >>> No need to buy a $10,000 proprietary desktop GIS... >>> >>> David. >>> >>> On Wed, Jun 15, 2011 at 1:33 PM, James David Smith >>> wrote: >>>> >>>> Dear Cristian & David, >>>> >>>> Thanks alot for your responses. >>>> >>>> Cristian - I don't have money to buy additional software >>>> unfortunately. With regard to 'view' I actually don't know what this >>>> is. I'm a bit of a beginner. I wonder if I made a 'view' whether I >>>> could then query that view with QGIS. Something for me to look into... >>>> >>>> David - That looks like a good idea... but when adding data to QGIS >>>> you can only select the whole table (I think) and then you manually >>>> enter the 'WHERE' clause in a dialog box. I guess I could run the >>>> query you suggest in PostgreSQL and save it into another table... and >>>> then link QGIS to the new table. Though it all starts to get a bit >>>> convoluted if I have to do that every time I want to look at some data >>>> in QGIS... >>>> >>>> Cheers >>>> >>>> James >>>> >>>> >>>> >>>> On 15 June 2011 19:16, David Fawcett wrote: >>>>> >>>>> This is what is really cool about using a database to store your >>>>> data... >>>>> >>>>> Change your query to: >>>>> >>>>> SELECT mycol1, >>>>> mycol2, >>>>> date_time_of_arrival as dt_arrive, >>>>> date_time_of_departure as dt_depart, >>>>> the_geom >>>>> FROM incidents >>>>> WHERE urgency = 'Immediate' >>>>> AND date_time_of_arrival - date_time_unit_assigned > '00:12:00' >>>>> >>>>> Note: you will need to specify the names of the columns that you are >>>>> interested in. >>>>> >>>>> >>>>> On Wed, Jun 15, 2011 at 12:58 PM, James David Smith >>>>> wrote: >>>>>> >>>>>> Dear all, >>>>>> >>>>>> I appreciate that this is more of a QGIS query, but I think that the >>>>>> route of the problem might be in PostGIS, so... I have opened QGIS and >>>>>> ran the query on my PostGIS table as below: >>>>>> >>>>>> SELECT * FROM incidents >>>>>> WHERE urgency = 'Immediate' >>>>>> AND date_time_of_arrival - date_time_unit_assigned > '00:12:00' >
Re: [postgis-users] QGIS - PostGIS - ArcGIS Interaction
Dear all, Thank you so much for your help. shp2pgsql - I didn't realise it automaticaly renamed columns. Awesome. I'll use that. ArcGIS 10 - I'm on 9 unfortunately. No access to 10 at the moment. RT SQL - I glanced at that the other day and they seem to have stopped supporting ArcGIS 9, and also you had to pay for anything more than 100 rows of data I think? I may have read that incorrectly, but the shp2pgsql solution seems good so I'll just go with that anyway. Cheers all, this list is awesome! James :-) On 15 June 2011 21:44, David Fawcett wrote: > James, > > There a couple of SQL plugins for QGIS. They allow you to visualize > spatial features via 'ad hoc' queries (not whole tables). > > I am pretty sure that I used the RT_SQL plugin. Here is a link to > some info: > http://underdark.wordpress.com/2010/10/16/visualizing-postgis-queries-in-qgis-using-rt-sql-layer-plugin/ > > No need to buy a $10,000 proprietary desktop GIS... > > David. > > On Wed, Jun 15, 2011 at 1:33 PM, James David Smith > wrote: >> Dear Cristian & David, >> >> Thanks alot for your responses. >> >> Cristian - I don't have money to buy additional software >> unfortunately. With regard to 'view' I actually don't know what this >> is. I'm a bit of a beginner. I wonder if I made a 'view' whether I >> could then query that view with QGIS. Something for me to look into... >> >> David - That looks like a good idea... but when adding data to QGIS >> you can only select the whole table (I think) and then you manually >> enter the 'WHERE' clause in a dialog box. I guess I could run the >> query you suggest in PostgreSQL and save it into another table... and >> then link QGIS to the new table. Though it all starts to get a bit >> convoluted if I have to do that every time I want to look at some data >> in QGIS... >> >> Cheers >> >> James >> >> >> >> On 15 June 2011 19:16, David Fawcett wrote: >>> This is what is really cool about using a database to store your data... >>> >>> Change your query to: >>> >>> SELECT mycol1, >>> mycol2, >>> date_time_of_arrival as dt_arrive, >>> date_time_of_departure as dt_depart, >>> the_geom >>> FROM incidents >>> WHERE urgency = 'Immediate' >>> AND date_time_of_arrival - date_time_unit_assigned > '00:12:00' >>> >>> Note: you will need to specify the names of the columns that you are >>> interested in. >>> >>> >>> On Wed, Jun 15, 2011 at 12:58 PM, James David Smith >>> wrote: >>>> Dear all, >>>> >>>> I appreciate that this is more of a QGIS query, but I think that the >>>> route of the problem might be in PostGIS, so... I have opened QGIS and >>>> ran the query on my PostGIS table as below: >>>> >>>> SELECT * FROM incidents >>>> WHERE urgency = 'Immediate' >>>> AND date_time_of_arrival - date_time_unit_assigned > '00:12:00' >>>> >>>> This gives me 33 points in QGIS. Great. I would now however like to >>>> save this as a ShapeFile for use in ArcGIS with which I am more >>>> familiar, but when I try to SAVE AS from QGIS I am given the following >>>> error >>>> >>>> Export to vector file failed. >>>> Error: trimming attribute name 'date_time_of_arrival' to ten >>>> significant characters produces duplicate column name. >>>> >>>> I am guessing that the problem here is that ESRI shapefiles can only >>>> deal with column names that are ten characters long...? And that as I >>>> also have a column called 'date_time_of_departure', when QGIS trims >>>> this column it ends up with two columns called the same thing? Is >>>> there a way around this? I realise I could rename the column in QGIS, >>>> but I actually have around 45 columns in the table and this problem >>>> will occur with about 15 pairs of them I think. I guess there is no >>>> simple answer... but thought I'd ask. >>>> >>>> Cheers >>>> >>>> James >>>> ___ >>>> 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 > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] QGIS - PostGIS - ArcGIS Interaction
Dear Cristian & David, Thanks alot for your responses. Cristian - I don't have money to buy additional software unfortunately. With regard to 'view' I actually don't know what this is. I'm a bit of a beginner. I wonder if I made a 'view' whether I could then query that view with QGIS. Something for me to look into... David - That looks like a good idea... but when adding data to QGIS you can only select the whole table (I think) and then you manually enter the 'WHERE' clause in a dialog box. I guess I could run the query you suggest in PostgreSQL and save it into another table... and then link QGIS to the new table. Though it all starts to get a bit convoluted if I have to do that every time I want to look at some data in QGIS... Cheers James On 15 June 2011 19:16, David Fawcett wrote: > This is what is really cool about using a database to store your data... > > Change your query to: > > SELECT mycol1, > mycol2, > date_time_of_arrival as dt_arrive, > date_time_of_departure as dt_depart, > the_geom > FROM incidents > WHERE urgency = 'Immediate' > AND date_time_of_arrival - date_time_unit_assigned > '00:12:00' > > Note: you will need to specify the names of the columns that you are > interested in. > > > On Wed, Jun 15, 2011 at 12:58 PM, James David Smith > wrote: >> Dear all, >> >> I appreciate that this is more of a QGIS query, but I think that the >> route of the problem might be in PostGIS, so... I have opened QGIS and >> ran the query on my PostGIS table as below: >> >> SELECT * FROM incidents >> WHERE urgency = 'Immediate' >> AND date_time_of_arrival - date_time_unit_assigned > '00:12:00' >> >> This gives me 33 points in QGIS. Great. I would now however like to >> save this as a ShapeFile for use in ArcGIS with which I am more >> familiar, but when I try to SAVE AS from QGIS I am given the following >> error >> >> Export to vector file failed. >> Error: trimming attribute name 'date_time_of_arrival' to ten >> significant characters produces duplicate column name. >> >> I am guessing that the problem here is that ESRI shapefiles can only >> deal with column names that are ten characters long...? And that as I >> also have a column called 'date_time_of_departure', when QGIS trims >> this column it ends up with two columns called the same thing? Is >> there a way around this? I realise I could rename the column in QGIS, >> but I actually have around 45 columns in the table and this problem >> will occur with about 15 pairs of them I think. I guess there is no >> simple answer... but thought I'd ask. >> >> Cheers >> >> James >> ___ >> 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] QGIS - PostGIS - ArcGIS Interaction
Dear all, I appreciate that this is more of a QGIS query, but I think that the route of the problem might be in PostGIS, so... I have opened QGIS and ran the query on my PostGIS table as below: SELECT * FROM incidents WHERE urgency = 'Immediate' AND date_time_of_arrival - date_time_unit_assigned > '00:12:00' This gives me 33 points in QGIS. Great. I would now however like to save this as a ShapeFile for use in ArcGIS with which I am more familiar, but when I try to SAVE AS from QGIS I am given the following error Export to vector file failed. Error: trimming attribute name 'date_time_of_arrival' to ten significant characters produces duplicate column name. I am guessing that the problem here is that ESRI shapefiles can only deal with column names that are ten characters long...? And that as I also have a column called 'date_time_of_departure', when QGIS trims this column it ends up with two columns called the same thing? Is there a way around this? I realise I could rename the column in QGIS, but I actually have around 45 columns in the table and this problem will occur with about 15 pairs of them I think. I guess there is no simple answer... but thought I'd ask. Cheers James ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Viewer plugins of PostGIS --HELP
Dear Paolo, Can you give me more information about the 'RT SQL layer plugin' you mention please? I use GQIS to visualise my data from PostGIS sometimes, but do find the query tool a bit limited and wonder whether this might be the solution...? Thanks James On 15 June 2011 10:04, Paolo Cavallini wrote: > Il giorno mer, 15/06/2011 alle 16.29 +0800, Ben Madin ha scritto: >> This may not help, but I guess I find QGIS quite simple for actually >> visualising data. Maybe not so good for complex queries. There are now >> a number of PostGIS plug-ins though. > > Confirmed. You can also run complex queries via the RT SQL layer plugin, > and others. > All the best. > -- > Paolo Cavallini: http://www.faunalia.it/pc > > ___ > 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] Update Column with Select
Hi all, Would love some help with this update query please. I have two tables with three columns... Table: vehicles Columns: date, incident, incident_link Table: incident Columns: date, incident, key What I would like to do is when the columns 'incident.date' AND 'incident.incident' match both the columns 'vehicles.date' and 'vehicles.incident' , I would like to copy the value from incident.key into the vehicles.incident_link column. Basically I am trying to create a foreign key link between the two tables - where one value in the incidents table is linked to multiple values in the vehicles table. I have been trying the below, and whilst it runs ok, when I look at the incident_link field afterwards it is blank. UPDATE vehicles SET incident_link = (SELECT key FROM vehicles, incidents WHERE 'incidents.date_of_incident' = 'vehicles.date' AND 'incidents.incident_id' = 'vehicles.incident' ) Any help would be appreciated please. Thank you James ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
