Re: [postgis-users] Odd question
That's fantastic! Exactly what I was looking for. Thank you very much. On Sat, Sep 15, 2012 at 6:44 PM, Paragon Corporation wrote: > Eric, > > Did y ou want to change the default or set it to 0 or something? > > We were meaning to expose that but wasn't sure if anyone would be > interested > in changing it. > > The setting is on the function: > > interpolate_from_address > > Just change the hardcoded default of 10 to what you want. > > > > CREATE OR REPLACE FUNCTION interpolate_from_address(given_address INTEGER, > in_addr1 VARCHAR, in_addr2 VARCHAR, in_road GEOMETRY, > in_side VARCHAR DEFAULT '',in_offset_m float DEFAULT 10 ); > > > In 2.1. we'll add to the list to allow this to be voerrided at the geocode > function level as an additional arg. Can't do that with the 2.0 one though > since that would change the api. > > Hope that helps, > Regina > http://www.postgis.us > > > > -Original Message- > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Eric > Aspengren > Sent: Saturday, September 15, 2012 5:20 PM > To: PostGIS Users Discussion > Subject: [postgis-users] Odd question > > Anybody know how to remove the default 10 meter offset from the PostGIS > geocoder? > > > -- > Eric Aspengren > (402) 478-8683 > www.streetlevelstrategies.com > ericas...@gmail.com > > > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > -- Eric Aspengren (402) 478-8683 www.streetlevelstrategies.com ericas...@gmail.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Odd question
Anybody know how to remove the default 10 meter offset from the PostGIS geocoder? -- Eric Aspengren (402) 478-8683 www.streetlevelstrategies.com ericas...@gmail.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] QGIS issues
Anybody have any issues with QGIS not displaying PostGIS layers? -- Eric Aspengren (402) 478-8683 www.streetlevelstrategies.com ericas...@gmail.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Help with PL/PGSQL!
Well, that was simple enough! Thanks! (as Eric rolls his eyes at himself)
On Mon, Jul 30, 2012 at 3:13 PM, David William Bitner wrote:
> You have geocoded declared as a type record. ST_Within needs that to be of
> type geometry.
>
> On Mon, Jul 30, 2012 at 1:32 PM, Eric Aspengren wrote:
>
>> So, I've got this PL/PGSQL script that doesn't want to work. I've got the
>> TIGER geocoder up and running and I can get whatever I want from that.
>> However, when I try and combine ST_Within with the output from GEOCODE I
>> get an error. I assume there's a simple syntax error here, but I can't
>> figure it out. I've been able to get this to work when just cutting and
>> pasting the actual geometry data into where "geocoded" is below, but
>> replacing it with the variable name gives me an error (sldu is a table with
>> Senate districts and sldust is the district number column):
>>
>> CREATE OR REPLACE FUNCTION get_district(address text)
>> RETURNS text AS
>> $$
>> DECLARE
>> district RECORD;
>> geocoded RECORD;
>> BEGIN
>> SELECT geomout into geocoded from geocode(address) as g;
>> SELECT sldust from sldu into district where ST_Within(geocoded,
>> the_geom);
>> return district;
>> END;
>> $$
>> LANGUAGE 'plpgsql' IMMUTABLE;
>>
>>
>> geocoder=# select get_district('1700 C St Lincoln, NE');
>>
>>
>> ERROR: function st_within(record, geometry) does not exist
>> LINE 1: SELECT sldust from sldu where ST_Within( $1 , the_geom)
>> ^
>> HINT: No function matches the given name and argument types. You might
>> need to add explicit type casts.
>> QUERY: SELECT sldust from sldu where ST_Within( $1 , the_geom)
>> CONTEXT: PL/pgSQL function "get_district" line 6 at SQL statement
>>
>> --
>> Eric Aspengren
>>
>> ___
>> postgis-users mailing list
>> postgis-users@postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>
>
> --
>
> David William Bitner
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
--
Eric Aspengren
(402) 478-VOTE
ericas...@gmail.com
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Help with PL/PGSQL!
So, I've got this PL/PGSQL script that doesn't want to work. I've got the
TIGER geocoder up and running and I can get whatever I want from that.
However, when I try and combine ST_Within with the output from GEOCODE I
get an error. I assume there's a simple syntax error here, but I can't
figure it out. I've been able to get this to work when just cutting and
pasting the actual geometry data into where "geocoded" is below, but
replacing it with the variable name gives me an error (sldu is a table with
Senate districts and sldust is the district number column):
CREATE OR REPLACE FUNCTION get_district(address text)
RETURNS text AS
$$
DECLARE
district RECORD;
geocoded RECORD;
BEGIN
SELECT geomout into geocoded from geocode(address) as g;
SELECT sldust from sldu into district where ST_Within(geocoded,
the_geom);
return district;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;
geocoder=# select get_district('1700 C St Lincoln, NE');
ERROR: function st_within(record, geometry) does not exist
LINE 1: SELECT sldust from sldu where ST_Within( $1 , the_geom)
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY: SELECT sldust from sldu where ST_Within( $1 , the_geom)
CONTEXT: PL/pgSQL function "get_district" line 6 at SQL statement
--
Eric Aspengren
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] oddity with computing areas
On Tue, Jul 17, 2012 at 3:48 PM, Mike Toews wrote: > On 18 July 2012 09:46, Mike Toews wrote: >> with data as (select 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'::geometry AS geom1, >> 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0, 1 0, 1 1, 0 1, 0 0))'::geometry AS geom2) >> select st_isvalid(geom1), st_area(geom1), >>st_isvalid(geom2), st_area(geom2) >> from data; > > Hmm, on further analysis, valid geometries shouldn't matter as much > for ST_Intersection: > > with data as (select 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'::geometry AS geom1, > 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0, 1 0, 1 1, 0 1, 0 0))'::geometry AS geom2) > select > st_isvalid(st_intersection(geom1, geom1)), > st_area(st_intersection(geom1, geom1)) > from data; > > -MIke Ok, I found the problem and it was user error. At one point the areas were getting treated as strings, and the string values were getting truncated, which was losing things like 'e-007' off the end. When the programmer fixed that, the problem went away. Sorry for the noise. -Eric - ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] oddity with computing areas
Hello. I'm seeing behavior I don't understand when computing the area of the intersection of two geometries. Given the columns geom_a and geom_b, both MULTIPOLYGON geometries with the same SRID (4269 in this case), I'm selecting st_area(st_intersection(geom_a, geom_b)). In most cases, I'm getting results that look correct. But in a small percentage of cases, I'm getting cases where the area is much larger than expected (i.e. as much as 30x greater than the larger of st_area(geom_a) and st_area(geom_b)). It seems to me that it should always hold that the area of the smaller of two geometries should be the upper bound for the area of their intersection. I'm only seeing the issue in cases where the two geometries intersect, but neither contains the other. Does anyone have any ideas as to what could cause this? Thank you in advance for any help, -Eric ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Census loader
Well now. I see to have discovered, finally, why I was having trouble getting the census loader to work. The script generated wasn't complete. I seem to have a corrupted .sql file. I don't even know where that is in the install. Where would that file be and where can I find a full version of that .sql file to fix it? Eric Aspengren ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Whew
thanks for the follow up! I may put in a ticket. On Fri, Jul 13, 2012 at 2:26 AM, Paragon Corporation wrote: > ** > Not included at the moment. Would require putting an entry in the > loader_generate_tables and also companion structures for each table in > tiger schema and mostly a lot of testing > mostly because census seems to like to change field names etc. every year. > > It's something we've thought about just adding these common entries, but > having them possibly disabled by default so people could choose to load > them if they wanted. > > If you are interested you can put in a ticket: > > http://trac.osgeo.org/postgis/ > > And mark it under tiger geocoder and version 2.1. > > We don't plan to make any more changes (aside from major bug fixes) to the > tiger_2010 (pacakged with 2.0) since tiger_2011 is already vastly > different and more efficient in the way it loads and the client that is > funding most of our work on tiger geocoder is using the tiger_2011 one > packaged with PostGIS 2.1 (though using it on a PostGIS 2.0 database) > > Thanks, > Regina and Leo > http://www.postgis.us > > > -- > *From:* postgis-users-boun...@postgis.refractions.net [mailto: > postgis-users-boun...@postgis.refractions.net] *On Behalf Of *Eric > Aspengren > *Sent:* Thursday, July 12, 2012 11:20 AM > *To:* PostGIS Users Discussion > *Subject:* [postgis-users] Whew > > Ok, so, I've finally gotten a functional geocoder up and running. > > Has anyone had any luck using the scripts provided to load other Tiger > files (Congressional Districts, Statehouse Districts, Etc.)? > > -- > Eric Aspengren > (402) 478-VOTE > ericas...@gmail.com > > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > -- Eric Aspengren (402) 478-VOTE ericas...@gmail.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Whew
Ok, so, I've finally gotten a functional geocoder up and running. Has anyone had any luck using the scripts provided to load other Tiger files (Congressional Districts, Statehouse Districts, Etc.)? -- Eric Aspengren (402) 478-VOTE ericas...@gmail.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] I'm so close!
Please ignore this note. I solved the issue. It was very simple, of course. On Mon, Jul 9, 2012 at 11:30 AM, Eric Aspengren wrote: > And yet... > > I've gotten just about everything done in a PostGIS/Tiger Geocoder install > and I get to create_geocode.sh and get a few problems. > > I couldn't this problem being addressed on this list or anywhere else. Any > ideas? > > I get this after all the tables are generated: > > psql:census_loader.sql:102: NOTICE: function > loader_generate_census(text[],text) does not exist, skipping > DROP FUNCTION > CREATE FUNCTION > DELETE 0 > INSERT 0 1 > INSERT 0 1 > INSERT 0 1 > create_census_base_tables > --- > Tables already present > (1 row) > > SET > BEGIN > psql:create_geocode.sql:23: NOTICE: type "norm_addy" does not exist, > skipping > DROP TYPE > CREATE TYPE > CREATE FUNCTION > CREATE FUNCTION > CREATE FUNCTION > psql:utility/levenshtein_ignore_case.sql:5: ERROR: function > levenshtein(text, text) does not exist > LINE 3: SELECT levenshtein(upper($1), upper($2)); > ^ > HINT: No function matches the given name and argument types. You might > need to add explicit type casts. > psql:normalize/end_soundex.sql:17: ERROR: current transaction is aborted, > commands ignored until end of transaction block > psql:normalize/count_words.sql:32: ERROR: current transaction is aborted, > commands ignored until end of transaction block > psql:normalize/state_extract.sql:90: ERROR: current transaction is > aborted, commands ignored until end of transaction block > psql:normalize/get_last_words.sql:29: ERROR: current transaction is > aborted, commands ignored until end of transaction block > psql:normalize/location_extract_countysub_exact.sql:56: ERROR: current > transaction is aborted, commands ignored until end of transaction block > psql:normalize/location_extract_countysub_fuzzy.sql:89: ERROR: current > transaction is aborted, commands ignored until end of transaction block > psql:normalize/location_extract_place_exact.sql:62: ERROR: current > transaction is aborted, commands ignored until end of transaction block > psql:normalize/location_extract_place_fuzzy.sql:90: ERROR: current > transaction is aborted, commands ignored until end of transaction block > psql:normalize/location_extract.sql:85: ERROR: current transaction is > aborted, commands ignored until end of transaction block > psql:normalize/normalize_address.sql:708: ERROR: current transaction is > aborted, commands ignored until end of transaction block > psql:normalize/pprint_addy.sql:10: ERROR: current transaction is aborted, > commands ignored until end of transaction block > psql:normalize/pprint_addy.sql:43: ERROR: current transaction is aborted, > commands ignored until end of transaction block > psql:geocode/other_helper_functions.sql:14: ERROR: current transaction is > aborted, commands ignored until end of transaction block > psql:geocode/other_helper_functions.sql:22: ERROR: current transaction is > aborted, commands ignored until end of transaction block > psql:geocode/other_helper_functions.sql:32: ERROR: current transaction is > aborted, commands ignored until end of transaction block > psql:geocode/other_helper_functions.sql:48: ERROR: current transaction is > aborted, commands ignored until end of transaction block > psql:geocode/other_helper_functions.sql:58: ERROR: current transaction is > aborted, commands ignored until end of transaction block > psql:geocode/other_helper_functions.sql:201: ERROR: current transaction > is aborted, commands ignored until end of transaction block > psql:geocode/other_helper_functions.sql:213: ERROR: current transaction > is aborted, commands ignored until end of transaction block > psql:geocode/other_helper_functions.sql:240: ERROR: current transaction > is aborted, commands ignored until end of transaction block > psql:geocode/other_helper_functions.sql:252: ERROR: current transaction > is aborted, commands ignored until end of transaction block > psql:geocode/rate_attributes.sql:29: ERROR: current transaction is > aborted, commands ignored until end of transaction block > psql:geocode/rate_attributes.sql:80: ERROR: current transaction is > aborted, commands ignored until end of transaction block > psql:geocode/includes_address.sql:94: ERROR: current transaction is > aborted, commands ignored until end of transaction block > psql:geocode/interpolate_from_address.sql:94: ERROR: current transaction > is aborted, commands ignored until end of transaction block > psql:geocode/geocode_address.sql:435: ERROR: current transaction is > aborted, commands ignored until end of transaction block > psql:geocode/geo
[postgis-users] I'm so close!
of transaction block psql:geocode/census_tracts_functions.sql:57: ERROR: current transaction is aborted, commands ignored until end of transaction block ROLLBACK CREATE FUNCTION ERROR: function least_hn(character varying, character varying) does not exist LINE 1: ...idx_tiger_addr_least_address ON addr USING btree (least_hn(f... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- Eric Aspengren (402) 478-VOTE ericas...@gmail.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Amazon EC2 and PostGIS 2.0
Has anyone on this list successfully deployed PostGIS 2.0 on AWS? I'm wondering what particular instance people would recommend (Amazon Linux, SuSE, RedHat, Etc)? This will be explicitly for geocoding and such for a fairly large dataset. -- Eric Aspengren (402) 478-VOTE ericas...@gmail.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Compilation problem for Postgis-1.5.4 on Debian Squeeze
Hello, Ive some problems to make Postgis-1.5.4 compilation On Debian Squeeze. After the ./configure , make command end like that: gcc -g -O2 -g -Wall -O2 -fPIC -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g -fpic -I/usr/include -I/usr/include/libxml2 -I../liblwgeom -I. -I. -I/usr/include/postgresql/9.0/server -I/usr/include/postgresql/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include/tcl8.5 -c -o lwgeom_cache.o lwgeom_cache.c In file included from lwgeom_cache.h:21, from lwgeom_cache.c:16: lwgeom_geos_prepared.h:60: erreur: expected :, ,, ;, } or __attribute__ before * token make[1]: *** [lwgeom_cache.o] Erreur 1 make[1]: quittant le répertoire « /source/postgis-1.5.4/postgis » make: *** [postgis] Erreur 2 gcc (Debian 4.4.5-8) 4.4.5 on Debian Squeeze The ./configure result: PostGIS is now configured for i686-pc-linux-gnu -- Compiler Info - C compiler: gcc -g -O2 C++ compiler: g++ -g -O2 -- Dependencies -- GEOS config: /usr/bin/geos-config GEOS version: 3.2.0 PostgreSQL config:/usr/bin/pg_config PostgreSQL version: PostgreSQL 9.0.7 PROJ4 version:47 Libxml2 config: /usr/bin/xml2-config Libxml2 version: 2.7.8 PostGIS debug level: 0 Documentation Generation xsltproc: /usr/bin/xsltproc xsl style sheets: dblatex: /usr/bin/dblatex convert: /usr/bin/convert Thanks for any idea Eric ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Strange ST_Area problem
On Sat, Mar 24, 2012 at 11:06 AM, Stephen Woodbridge wrote: > st_area returns in the same units as the input data. So data in 4326 is > degrees and you numbers are in degrees squared. If you want the number in > meters or feet then project then into an appropriate UTM or equal area > projection. Ah.. that makes sense. Would EPSG:32230 be appropriate? (WGS 72, UTM zone 30N) I'm dealing with things roughly in the 30.0 to 31.0N range. > - Steve Thanks! -- Eric Ladner ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Strange ST_Area problem
I've got some building outlines that I'm pretty sure I've worked with before. They are all srid 4326 (lat/lon), converted from a SHP file. ST_Area returns extremely small numbers for any of the buildings. It's almost like it's looking at the data like they are absolute numbers instead of lat/lon. Thoughts? As an example: select st_npoints(the_geom), st_srid(the_geom), st_summary(the_geom), st_area(st_transform(the_geom,4326)) from buildings; st_npoints | st_srid |st_summary| st_area +-+--+-- 5 |4326 | | 9.7305773438805e-09 : MultiPolygon[BS] with 1 elements : Polygon[] with 1 rings :ring 0 has 5 points : 5 |4326 | | 1.1065881433403e-08 : MultiPolygon[BS] with 1 elements : Polygon[] with 1 rings :ring 0 has 5 points : -- Eric Ladner ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Server Question
I just dove in and set myself up with Amazon EC2. I'll see if I can come up with a tutorial on installing stuff, if I happen to succeed. On Mon, Mar 12, 2012 at 11:44 AM, Andy Colson wrote: > On 3/12/2012 10:27 AM, Eric Aspengren wrote: > >> I have a couple of questions about getting some server space on the cloud. >> 1) anybody have any luck with this? >> 2) are the servers fast enough to do serious geocoding and such? >> 3) any recommendations? >> 4) what about PostGIS 2.0? anybody planning to support that/already >> supporting the Beta? >> >> -- >> Eric Aspengren >> (402) 478-VOTE >> ericas...@gmail.com <mailto:ericas...@gmail.com> >> >> > I use mod_perl and PG, which nobody supports. I have a small Linode > account for it. Its great because you get complete control -- run > whatever, whenever. > > -Andy > > -- Eric Aspengren (402) 478-VOTE ericas...@gmail.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Server Question
I have a couple of questions about getting some server space on the cloud. 1) anybody have any luck with this? 2) are the servers fast enough to do serious geocoding and such? 3) any recommendations? 4) what about PostGIS 2.0? anybody planning to support that/already supporting the Beta? -- Eric Aspengren (402) 478-VOTE ericas...@gmail.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PHP & PostGIS.
Works great! Thanks for the insight.
On Sun, Jun 26, 2011 at 9:33 PM, Stephen Woodbridge wrote:
> On 6/27/2011 12:31 AM, Stephen Woodbridge wrote:
>
>> On 6/27/2011 12:28 AM, Eric Sepich wrote:
>>
>>> >> $dbconn = pg_connect("host=localhost port=5432 dbname=postgismethane
>>> user=root password=excedrin413");
>>>
>>> $query = "SELECT * FROM polygons WHERE gid = 1";
>>> $result = pg_exec($dbconn, $query);
>>> if (!$result) {printf ("ERROR"); exit;}
>>> $numrows = pg_numrows($result);
>>> ?>
>>>
>>> I get one row returned but I just don't seem to have any kind of
>>> documentation on what to do with it. I want to list the coordinates of
>>> the polygon returned by my query. Does anyone know how to do that?
>>>
>>
>> Try something like this:
>>
>>
>> > $dbconn = pg_connect("host=localhost port=5432 dbname=postgismethane
>> user=root password=excedrin413");
>>
>> $query = "SELECT * FROM polygons WHERE gid = 1";
>>
>
> Ooops, meant to change this to:
>
> $query = "SELECT *, astext(the_geom) FROM polygons WHERE gid = 1";
>
>
> $r = pg_exec($dbconn, $query);
>> if (!$r) {printf ("ERROR"); exit;}
>>
>> $n = pg_num_rows($r);
>> for ($i=0; $i<$n; $i++) {
>> printf("- Row: %d ---\n", $i);
>> $row = pg_fetch_assoc($r);
>> pg_free_result($r);
>> foreach ($row as $k=>$v)
>> printf("%20s = %s\n", $k, $v);
>> }
>> pg_close($dbh);
>> ?>
>> __**_
>> postgis-users mailing list
>> postgis-users@postgis.**refractions.net
>> http://postgis.refractions.**net/mailman/listinfo/postgis-**users<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<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] PHP & PostGIS.
I get one row returned but I just don't seem to have any kind of documentation on what to do with it. I want to list the coordinates of the polygon returned by my query. Does anyone know how to do that? Thank you, Eric S. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Get point distance and bearing from known point.
I need a function that gets a point a distance and bearing from a known point on the WGS84 datumn. If postgis has such a function I think I will start using it! I checked the reference manual but my limited knowledge of postgis at this point is not doing me well. Thank you! ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] 2010 Census
I know this has likely been covered on this list before. So, pardon if this is redundant, I just signed up. I'm looking for a good tutorial to get the new 2010 Census data for a state loaded into a PostGIS database, including all the recent Tiger files and demographic data. Is there a good one out there? -- Eric Aspengren Data Manager Planned Parenthood of the Heartland (402) 478-VOTE ericas...@gmail.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Creating a grid purely in SQL
Hi Puneet, This is typically how I've done it. This example query builds a .25 degree grid around Pennsylvania. There are probably better ways select row, col, geom from ( select yseries+1 as row,xseries+1 as col,st_setsrid(st_translate(geom,0,yseries * .25),4326) as geom from ( select xseries, generate_series(0,15,1) as yseries, st_translate(geom,xseries * .25,0) as geom from ( select generate_series(0,27,1) as xseries, st_envelope(st_makeline(st_pointn(st_boundary(geom),1), st_makepoint(st_x(st_pointn(st_boundary(geom),1)) + .25, st_y(st_pointn(st_boundary(geom),1)) + .25))) as geom from ( select st_envelope(st_makebox2d(st_makepoint(-81,39),st_makepoint(-74,43))) as geom ) as g1 ) as g2 ) as g3 ) as g4 -Eric ...though the mischief arising from the study of words is prodigious, we must not consider it as the only cause of darkening the splendours of Truth, and obstructing the free diffusion of her light. Different manners and philosophies have equally contributed to banish the goddess from our realms, and to render our eyes offended with her celestial light. Hence we must not wonder that, being indignant at the change, and perceiving the empire of ignorance rising to unbounded dominion, she has retired from the spreading darkness, and concealed herself in the tranquil and divinely lucid regions of mind. -Thomas Taylor -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Mr. Puneet Kishor Sent: Monday, March 07, 2011 11:13 PM To: PostGIS Users Discussion Subject: [postgis-users] Creating a grid purely in SQL Given a top-left starting point [ulx, uly], and a cell width 'w' and height 'h', is it possible to create a table entirely in SQL populated with rows increasing from left to right up to X and top to bottom up to Y. The table schema would be something like -- CREATE TABLE cells ( cell_id INTEGER NOT NULL, xmid DOUBLE PRECISION, ymid DOUBLE PRECISION, the_geom GEOMETRY, CONSTRAINT cells_pkey PRIMARY KEY (cell_id) ); where xmid = (xmin + xmax) / 2 and ymid = (ymin + ymax) / 2, [xmin, ymin, xmax, ymax] being the corners of each cell. A bonus question -- is it possible to store two geometry columns in one table? For example, if I wanted to store the geometry for both the center points [xmin, ymid] as well as the box [xmin, ymin, xmax, ymax], would that be possible? Would that even be recommended (for example, to speed up queries/drawing, etc.). Puneet. ___ 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] How to calculate the symmetric difference oflinestrings and polygons (i.e. linestrings which do notintersect polygons)
I guess what I meant was: select st_difference(streets,st_intersection(streets,forests)) as streets from streets, forests where st_intersects(streets,forests) union select streets from streets, forests where not st_intersects(streets,forests) -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net]On Behalf Of Randall, Eric Sent: Monday, January 31, 2011 6:41 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] How to calculate the symmetric difference oflinestrings and polygons (i.e. linestrings which do notintersect polygons) Seems like the difference of the streets with the intersection of the forests and the streets should do that. st_difference(streets,st_intersection(streets,forests)) Eric -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net]On Behalf Of Stefan Keller Sent: Saturday, January 29, 2011 9:04 PM To: PostGIS Users Discussion Subject: [postgis-users] How to calculate the symmetric difference of linestrings and polygons (i.e. linestrings which do not intersect polygons) Hi, Given streets linestrings and forest polygons, think of all streets segments which are *not* inside forest areas. How can I calculate all segments from linestrings which do not intersect polygons? Any ideas? - S. Attempt 1: ST_SymDifference sounds good! But even this equivalent produces nonsense: # SELECT ST_AsText( ST_CollectionExtract(ST_Difference(ST_Union(ls.way,po.way), ST_Intersection(ls.way,po.way)),2) ) FROM linestring ls, polygon po Attempt 2: # SELECT ST_AsText(ST_Intersection(ls.way, po.way)) FROM linestring ls, polygon po gives me all streets *inside* forests. But I'd like get the "remainder" in between... Attempt 3: # SELECT ST_AsText(ST_Difference(ls.way, po.way)) FROM linestring ls, polygon po gives me two displaced lines for each street (because the resultse contains the difference between one line and one polygon), Thesse two could be intersected - but ST_Intersection of two overlapping lines (with same nodes except end nodes) does'nt seem to work. Attempt 4: In a very desperate idea, I calculcated the extent of both tables in order generate a huge boundary polygon with has as many wholes as there are polygons. Then I could do an ST_Intersect of this big swiss cheese polygon with the lines - but no luck. Attempt 5: I finally tried to dump all points from Attempt 2 (intersection) to get hold of the boarder points where lines and polygons cross. But when tried to split a line at a point and to sort out all lines outside polygons things got again complicated.. ___ 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] How to calculate the symmetric difference of linestrings and polygons (i.e. linestrings which do not intersect polygons)
Seems like the difference of the streets with the intersection of the forests and the streets should do that. st_difference(streets,st_intersection(streets,forests)) Eric -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net]On Behalf Of Stefan Keller Sent: Saturday, January 29, 2011 9:04 PM To: PostGIS Users Discussion Subject: [postgis-users] How to calculate the symmetric difference of linestrings and polygons (i.e. linestrings which do not intersect polygons) Hi, Given streets linestrings and forest polygons, think of all streets segments which are *not* inside forest areas. How can I calculate all segments from linestrings which do not intersect polygons? Any ideas? - S. Attempt 1: ST_SymDifference sounds good! But even this equivalent produces nonsense: # SELECT ST_AsText( ST_CollectionExtract(ST_Difference(ST_Union(ls.way,po.way), ST_Intersection(ls.way,po.way)),2) ) FROM linestring ls, polygon po Attempt 2: # SELECT ST_AsText(ST_Intersection(ls.way, po.way)) FROM linestring ls, polygon po gives me all streets *inside* forests. But I'd like get the "remainder" in between... Attempt 3: # SELECT ST_AsText(ST_Difference(ls.way, po.way)) FROM linestring ls, polygon po gives me two displaced lines for each street (because the resultse contains the difference between one line and one polygon), Thesse two could be intersected - but ST_Intersection of two overlapping lines (with same nodes except end nodes) does'nt seem to work. Attempt 4: In a very desperate idea, I calculcated the extent of both tables in order generate a huge boundary polygon with has as many wholes as there are polygons. Then I could do an ST_Intersect of this big swiss cheese polygon with the lines - but no luck. Attempt 5: I finally tried to dump all points from Attempt 2 (intersection) to get hold of the boarder points where lines and polygons cross. But when tried to split a line at a point and to sort out all lines outside polygons things got again complicated.. ___ 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] defending postgresql - postgis.
RBFDB ? :-) Maybe Paul Ramsey can provide a link to his presentation on Open Source for Management (something like that). Sounds like it might be relevant in your case. I don't have the link handy, Paul ? Thanks. Eric -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net]On Behalf Of aperi2007 Sent: Monday, January 31, 2011 3:05 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] defending postgresql - postgis. >hello > >I am in favor of using OSS and postgresql - postgis. >I am challenged in my working area that how postgresql - postgis >are trustable and not having support. Just curious. What is the actul big DBMS of your company ? Andrea. ___ 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] How to remotely edit a postgis db over a webclient?
Robert, Might have a look at Geomajas: http://www.geomajas.org/ -Eric Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net]On Behalf Of Robert Buckley Sent: Wednesday, January 26, 2011 3:02 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] How to remotely edit a postgis db over a webclient? Hi, Slight variation of the first question, but still very relevant.If I have non-gis users who want to edit the data, are there any examples of webclients that could also do simplified editing?.. Yours, Robert ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Getting height profile from contour lines
4803155) > 58028 | 15 | 159 | 2.30 | POINT(320649.710451749 5777853.53161307) > 58011 | 16 | 160 | 6.24 | POINT(320624.965330361 5777867.98451583) > 58024 | 16 | 160 | 6.24 | POINT(320624.965330361 5777867.98451583) > 58006 | 15 | 160 | 10.32 | POINT(320586.767288447 5777830.54803155) > 58019 | 15 | 160 | 10.32 | POINT(320586.767288447 5777830.54803155) > 58015 | 16 | 160 | 14.56 | POINT(320594.200502687 598.8208976) > 58029 | 16 | 159 | 34.90 | POINT(320649.710451749 5777853.53161307) > 58014 | 15 | 160 | 42.91 | POINT(320594.200502687 598.8208976) > -- > Mit freundlichen Grüßen > > Matthias Ludwig > B.Eng. Geoinformation > > Tel.: 0177/4913288 > 030/68329587 > ICQ: 163168410 > > Neu: GMX De-Mail - Einfach wie E-Mail, sicher wie ein Brief! > Jetzt De-Mail-Adresse reservieren: http://portal.gmx.net/de/go/demail > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > -- Eric Ladner ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] New to postgis...
On Tue, Dec 7, 2010 at 11:14 AM, Kevin Neufeld wrote: > Well, your envelope here is actually a collapsed POLYGON, a LINESTRING, so > it would make sense that this particular query would return nothing. Oh.. I see that I've typo'ed the coords on the make envelope. Sigh.. > As for the others ... can you post a single small example of what you've > hand verified? Maybe we can spot something else going on. I'll dig out a simple example tonight. I've tried at least three or four different methods (st_contains, st_within, combinations of that with &&, etc..). I'll narrow it down to one concrete example that should return one or two rows of data from the whole table. > -- Kevin > > On 12/6/2010 7:41 PM, Eric Ladner wrote: >> >> select gid, objectid from gis_schema.jksn_cnty_buidings where >> ST_Within(the_geom,ST_AsText(ST_MakeEnvelope(-90, 30, -86, 30, >> 4269))); > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > -- Eric Ladner ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] New to postgis...
On Mon, Dec 6, 2010 at 10:23 PM, Stephen Woodbridge wrote: > 1. st_contains() mean wholey inside and not touching or cross the boundary > of the contains IIRC. Is this really what you want? Yes, I'm aware of that. I was using the && operator in the where clause also, but I took it out just to shorten the queries while I was working on getting SOMETHING to return... > 3. Some queries to try: > ... I tried both of those queries and both returned zero rows... :( I'll keep bashing on it, though.. -- Eric Ladner ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] New to postgis...
I just started using postgis in conjunction with OpenStreetMap and I've been having a heck of a time gettnig a query to work. I'm fairly well versed in SQL in general (Oracle DBA for 10+ years) but I'm new to the spatial stuff. I've got a bunch of plot line data downloaded from the state's GIS system loaded into a postgis database and I was trying to slice it up into manageable chunks to upload to OSM. In the table, I've got almost 130,000 distinct objects and I can view them with queries (like below... I shortened up the MULTIPOLYGON list for brevity..) select gid, ST_AsText(the_geom) from gis_schema.jksn_cnty_buidings where gid = 99; gid |st_astext -+- 99 | MULTIPOLYGON(((-88.7426828988773 30.40751849384,-88.7427319811994 30.4075187592759,-88.7426828988773 30.40751849384))) The theory goes like this: Find the extents of the data (xmax, ymax, xmin, ymin - this part works great), select min(ST_XMin(the_geom)) as "min_x", max(ST_XMax(the_geom)) as "max_x" from gis_schema.jksn_cnty_buidings ; select min(ST_YMin(the_geom)) as "min_y", max(ST_YMax(the_geom)) as "max_y" from gis_schema.jksn_cnty_buidings ; Then partition that space into 16 or 25 sub-areas with a Perl script that generates individual queries for the sub quadrants.. I planned on moving them to additional numbered tables (to avoid dupes when objects overlap the bounding polygon) then to handle each table as a separate upload. The examples below are just selects, though, all of which return zero rows. select * from gis_schema.jksn_cnty_buidings where _ST_Contains(the_geom,'POLYGON ((-88.981 32.0393, -88.8616 32.0393, -88.8616 32.4663, -88.981 32.4663, -88.981 32.0393))'); select * from gis_schema.jksn_cnty_buidings where _ST_Contains(the_geom,'POLYGON ((-88.8616 32.0393, -88.7422 32.0393, -88.7422 32.4663, -88.8616 32.4663, -88.8616 32.0393))'); There are thousands of polygons all across the area, and I've hand verified a couple. The above two queries should return about 2300 records each, but every time I run it, it returns zero. I've tried several variants like (including _ST and regular ST functions) select * from gis_schema.jksn_cnty_buidings where the_geom && 'POLYGON ((-88.5034 32.0393, -88.384 32.0393, -88.384 32.4663, -88.5034 32.4663, -88.5034 32.0393))' and _ST_Contains(the_geom,'POLYGON ((-88.5034 32.0393, -88.384 32.0393, -88.384 32.4663, -88.5034 32.4663, -88.5034 32.0393))'); And even select gid, objectid from gis_schema.jksn_cnty_buidings where ST_Within(the_geom,ST_AsText(ST_MakeEnvelope(-90, 30, -86, 30, 4269))); An ideas where I'm messing up here? -- Eric Ladner ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Selecting polygons circled by one only polygons
You could try making a polygon out of the interior ring and using that with a within or contains: st_buildarea(st_interiorringn(geometryn(geom,1),1)) -Eric -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net]on Behalf Of Andrea Peri Sent: Thursday, October 28, 2010 6:07 AM To: PostGIS Users Discussion Subject: [postgis-users] Selecting polygons circled by one only polygons Hi, I have a table of polygon where every polygon can be holed. And in the hole can be inside another polygon (another record of the table, no multipolygons). I need to detect all the polygon that are inside a hole of another polygon. Initially I think this was easy but after some test I notice this is not so easy because the polygon inside the hole is formerly disjoint from the polygon with hole. For more example I don't search the polygon that are full circled by more than 1 polygon, but only the polygon circled by 1 only polygon There is some method to detect this type of situations ? Thx, Andrea peri. -- - Andrea Peri . . . . . . . . . qwerty àèìòù - ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How best to build a regular grid from points
Hi Brent, I use something like this, should be adaptable to your use (but might not be the best way). this makes a 1 foot sided cell, and translates it 10x and 10y. Eric select st_translate(geom, 0 ,yseries) as geom from (select generate_series(0,9, 1) as yseries,st_translate(geom, xseries ,0) as geom from (select generate_series(0, 9, 1) as xseries, geom from (select st_envelope(st_makeline(st_makepoint(138,63),st_makepoint(139,64))) as geom) as t1) as t2) as t3 -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net]on Behalf Of pcr...@pcreso.com Sent: Sunday, October 17, 2010 1:39 PM To: postgis-users@postgis.refractions.net Subject: [postgis-users] How best to build a regular grid from points Hi, I have a set of 155,000,000 points on a regular grid. I need to convert these to a set of square polygons covering the area represented by the points. Can anyone suggest how this could be done in Postgis? Thanks, Brent Wood ___ 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] postgres data tables package (ORACLE Formsreplacement?)
John, For the web solution I use WaveMaker with but haven't done anything too fancy with it yet. http://www.wavemaker.com/ SQL Workbench might be another option. http://www.sql-workbench.net/ Eric Eric Randall GIS DB Admin/Analyst County of Erie 140 W 6th St Room 111 Erie, PA 16501 ph. 814-451-6063 fx. 814-451-7000 -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net]on Behalf Of John Callahan Sent: Thursday, August 05, 2010 9:54 AM To: PostGIS Users Discussion Subject: [postgis-users] postgres data tables package (ORACLE Formsreplacement?) Brief background: We're currently using Oracle for our main database. Nothing too big, 15 - 20 tables and the largest table has about 100K records. We use Oracle Forms as a web-based option for inserting and updating records. I am using Postgres/PostGIS for a couple of other projects. I would like to use Postgres for our main database as well. However, I would need to come up with a solution for inserting/updating the data tables within our group (a replacement for Oracle Forms). Preferably, a web-based solution (PHP, Python, etc...) but it could be a Windows desktop solution installed on each staff members machine. Something that maintains the data integrity (e.g., forcing users to enter a date when Date is required, etc...) Does anyone know of a FOSS package that can be used to manage data tables within a Postgres database? Updating data records is the highest priority since the built-in Admin tool can be used for managing users, creating new tables, etc..., and will be done only by a database manager. Thanks for any advice or guidance you can provide. - John ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Create POLYGON from Several LINESTRINGs
I think perhaps st_buildarea and st_collect might be what you want. Below is what I used to create a hexagon out of points->lines which was then translated (using generate_series) across X and Y to create a hexagonal grid for the County. There's probably a simpler way to make a hexagon but it illustrates the st_buildarea. Maybe that helps. -Eric --let's make a hexagon with 1000 foot sides and position it with it's --center at 121, 61 (the lower left of the pamap tile extent for Erie County) --and create a table drop table if exists hex1000; create table hex1000 as select translate(st_buildarea(st_collect(geom)),121, 61) as geom from (select makeline(st_endpoint(makeline(makepoint(0, 0),makepoint(0, - 1000))),st_endpoint(rotate(makeline(makepoint(0, 0),makepoint(0, -1000)), radians(300 as geom union select makeline(st_endpoint(rotate(makeline(makepoint(0, 0),makepoint(0, -1000)), radians(300))),st_endpoint(rotate(makeline(makepoint(0, 0),makepoint(0, -1000)), radians(240 as geom union select makeline(st_endpoint(rotate(makeline(makepoint(0, 0),makepoint(0, -1000)), radians(240))),st_endpoint(rotate(makeline(makepoint(0, 0),makepoint(0, -1000)), radians(180 as geom union select makeline(st_endpoint(rotate(makeline(makepoint(0, 0),makepoint(0, -1000)), radians(180))),st_endpoint(rotate(makeline(makepoint(0, 0),makepoint(0, -1000)), radians(120 as geom union select makeline(st_endpoint(rotate(makeline(makepoint(0, 0),makepoint(0, -1000)), radians(120))),st_endpoint(rotate(makeline(makepoint(0, 0),makepoint(0, -1000)), radians(60 as geom union select makeline(st_endpoint(rotate(makeline(makepoint(0, 0),makepoint(0, -1000)), radians(60))),st_endpoint(rotate(makeline(makepoint(0, 0),makepoint(0, -1000)), radians(0 as geom ) as t1; -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net]on Behalf Of Avery Penniston Sent: Tuesday, June 22, 2010 2:38 PM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] Create POLYGON from Several LINESTRINGs I'm trying to create 'pie wedge' shaped polygons by building them in a Postgres function. I am able to create the 2 straight chords and the arc as separate LINESTRINGs, and I have verified that each LINESTRING shares its endpoints with the other two LINESTRINGs. However, I am having trouble putting the individual parts together to form a POLYGON. I tried to ST_UNION the 3 LINESTRINGs together, and the result is a MULTILINESTRING. I then call ST_LINEMERGE to convert the MULTILINESTRING to a single LINESTRING so I can pass it to the ST_POLYGON function, but the result of the ST_LINEMERGE is a MULTILINESTRING. I tried using ST_COLLECT instead of ST_UNION, but then I got a GEOMETRYCOLLECTION containing a MULTILINESTRING and a LINESTRING. Does anybody have some advice for creating a POLYGON from 3 or more separate LINESTRINGs? ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] reverse geocoding
Rich, You should just not use st_geometryn then...since linestring is what is desired by the functions in the first place. Also, I find that the single query is slower than using separate table operations. -Eric -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net]on Behalf Of Richard Greenwood Sent: Saturday, April 03, 2010 11:28 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] reverse geocoding On Sat, Apr 3, 2010 at 11:05 AM, Randall, Eric wrote: > -- Here's my go at it. Paste into favorite sql editor for easier reading. > Cheers. -Eric Really nice! Initially my sides were NULL because my road centerlines were LINESTRING rather than MULTILINESTRING which caused ST_GeometryN() to return NULL. Used ST_Multi() to cast them, and am now getting good looking results. Thanks Eric, I owe you a few beers! 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 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Copying parallel line features
Oops, I should have said "ST_translate and ST_rotate" I think. Eric -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net]on Behalf Of Randall, Eric Sent: Wednesday, March 10, 2010 8:36 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] Copying parallel line features Couldn't you ST_translate to move your line to a 0,0 based on ST_startpoint, make a new paraallel using +/-X of vertices, then translate it back? Eric -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net]on Behalf Of Sascha Müller Sent: Wednesday, March 10, 2010 5:34 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] Copying parallel line features Hi all, How can I copying parallel line features with postgis? I search for something like the function "copay parallel" in ESRI ArcGIS. Is there a way to do this? Thanks Sascha ___ 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] Copying parallel line features
Couldn't you ST_translate to move your line to a 0,0 based on ST_startpoint, make a new paraallel using +/-X of vertices, then translate it back? Eric -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net]on Behalf Of Sascha Müller Sent: Wednesday, March 10, 2010 5:34 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] Copying parallel line features Hi all, How can I copying parallel line features with postgis? I search for something like the function "copay parallel" in ESRI ArcGIS. Is there a way to do this? Thanks Sascha ___ 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] Jairo Sanchez wants to connect on LinkedIn
I'd rather be LinkedOff than LinkedOn, but I'd rather be LinkedOn than LinkedIn. -Eric -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net]on Behalf Of Jairo Sanchez Sent: Monday, December 28, 2009 8:28 AM To: Debasish Sahu Subject: [postgis-users] Jairo Sanchez wants to connect on LinkedIn LinkedIn Jairo Sanchez requested to add you as a connection on LinkedIn: Debasish, I'd like to add you to my professional network on LinkedIn. - Jairo Sanchez Accept <http://www.linkedin.com/e/hCr-kpR-ellPe2HLEFrNkpRQGIeYGikvtqzlsEBBAOlWAkdAjTkFlyK/blk/I1684729859_2/pmpxnSRJrSdvj4R5fnhv9ClRsDgZp6lQs6lzoQ5AomZIpn8_cBYVdjwVczsQe3oNiiZWoBFLq4N6nOYOdP4VczkPdjALrCBxbOYWrSlI/EML_comm_afe/> View <http://www.linkedin.com/e/hCr-kpR-ellPe2HLEFrNkpRQGIeYGikvtqzlsEBBAOlWAkdAjTkFlyK/blk/I1684729859_2/39vejkUej8Td3wSckALqnpPbOYWrSlI/S2_svi/> invitation from Jairo Sanchez DID YOU KNOW you can conduct a more credible and powerful reference check using LinkedIn? Enter the company name and years of employment or the prospective employee to find their colleagues <http://www.linkedin.com/e/rsr/inv-27/> that are also in your network. This provides you with a more balanced set of feedback to evaluate that new hire. © 2009, LinkedIn Corporation ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Finding nearest crossing streets
Hi all, I'm currently finding nearest crossing-streets by procedurally walking upstream and downstream of any given centerline segment, looking at endpoints/startpoints and vice versa until a different street name occurs. Is there a better way to do this? Thanks! Eric ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] extending a line without having any elbow
Ahmet, I don't understand. Do you mean you want to extend the line backward and forward by the angles of its first and last 2-point segments respectively? Eric -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net]on Behalf Of Ahmet Temiz Sent: Tuesday, October 13, 2009 2:53 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] extending a line without having any elbow hello I was wondering how I could extend a line without having any bent ? (on the direction of both original end and starting points) regards Ahmet TEMİZ -- Open WebMail Project (http://openwebmail.org) -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ___ 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] Looking for examples of using CTE and recursivequeries with PostGIS
Wow, that's great! They do such nice work. I'm reading their book-in-progress too and it's great. Thanks. -Eric -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net]on Behalf Of courtin olivier Sent: Sunday, October 11, 2009 11:56 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] Looking for examples of using CTE and recursivequeries with PostGIS On Oct 11, 2009, at 5:46 PM, Randall, Eric wrote: Hi, > I've recently started using postgis 1.4 and pgsql 8.4 and wondered > if anyone could give examples of > using common table expressions and recursive queries in a spatial > setting? Regina & Leo wrote this for Oscon, with some CTE examples: <http://www.bostongis.com/PrinterFriendly.aspx?content_name=oscon2009_postgis_spatial_tricks > HTH, -- Olivier ___ 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] Looking for examples of using CTE and recursive queries with PostGIS
Hey all, I've recently started using postgis 1.4 and pgsql 8.4 and wondered if anyone could give examples of using common table expressions and recursive queries in a spatial setting? Thanks! -Eric ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] what if my table contain multipolygon
Then maybe use ST_geometryn to get a single polygon such as select generate_series(1, ST_npoints(geom)-1) as point_id , ST_pointn(ST_exteriorring(ST_geometryn(geom,1)),generate_series(1, ST_npoints(geom)-1)) as geom from polygontable -Eric - From: eehab hamzeh [mailto:eeha...@hotmail.com] Sent: Sunday, October 11, 2009 9:33 AM To: Randall, Eric Subject: what if my table contain multipolygon thanks eric for your replay What if my table contain multipolygon how can i adjust the sql statement select generate_series(1, ST_npoints(geom)-1) as point_id , ST_pointn(ST_exteriorring(geom),generate_series(1, ST_npoints(geom)-1)) as geom from polygontable . Kind regards _ Windows Live: Friends get your Flickr, Yelp, and Digg updates when they e-mail <http://www.microsoft.com/middleeast/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:en-xm:SI_SB_3:092010> you. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] point to list
Try, select generate_series(1, ST_npoints(geom)-1) as point_id , ST_pointn(ST_exteriorring(geom),generate_series(1, ST_npoints(geom)-1)) as geom from polygontable based on this post http://www.nabble.com/Re:-Splitting-linestring-into-line-segments-td16063883.html -Eric -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net]on Behalf Of eehab hamzeh Sent: Sunday, October 11, 2009 8:13 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] point to list Hello i try to have the points that make one polygon in one table as follow. i have the following polygon polygon (0 0, 0 1, 1 1, 1 0) i need it to be in a table where the results looks like id : the_geom 1 : point(0 0) 2 : point(0,1) 3 : point(1 1) 4 : point(1,0) i try to use st_dump(the geom) from polygon it does not work it is important to me to assign id for each point thanks _ Windows Live: Friends get your Flickr, Yelp, and Digg updates when they e-mail <http://www.microsoft.com/middleeast/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:en-xm:SI_SB_3:092010> you. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Slow Postgres Query
Thanks Stanly! So to make it a single step I guess... create table lines_from_points_and_points_projected_onto_centerlines as select gid as point_id, (SELECT gid from twothousandlines order by st_distance(twothousandlines.geom,sixtypoints.geom) limit 1 ) as line_id, (SELECT st_makeline(sixtypoints.geom,st_line_interpolate_point(st_geometryn(twothousandlines.geom,1),st_line_locate_point(st_geometryn(twothousandlines.geom,1),sixtypoints.geom))) from twothousandlines order by st_distance(twothousandlines.geom,sixtypoints.geom) limit 1 ) as geom from sixtypoints -Eric Eric Randall -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net]on Behalf Of Sufficool, Stanley Sent: Friday, October 02, 2009 3:32 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] Slow Postgres Query I usually avoid cursors when possible: insert into linespoints (point_id, nearest_line_id) select gid, ( SELECT gid from twothousandlines order by st_distance(twothousandlines.geom,sixtypoints.geom) limit 1 ) from sixtypoints > -Original Message- > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On > Behalf Of Randall, Eric > Sent: Friday, October 02, 2009 10:20 AM > To: PostGIS Users Discussion > Subject: Re: [postgis-users] Slow Postgres Query > > > Hi there dassouki, > > Right, no way that should take so long. I created 60 point > and 2000 line tables from addresses and road centerlines to > do a test. The tables are called "sixtypoints" and > "twothousandlines". Then created a "linespoints" table to > store point and nearest line ids: > > /* > create table linespoints ( > point_id integer, > nearest_line_id integer ); > */ > > Then created a function to insert the point and nearest line ids: > > /* > create or replace function insert_into_linespoints() returns > integer as $$ > > declare > > getrow cursor for select * from sixtypoints; > > point_row sixtypoints%rowtype; > > v_nearest_line_id integer; > max_rows integer; > rowcount integer; > > begin > > delete from linespoints; > > select into max_rows count(*) from sixtypoints; > > open getrow; > rowcount := 0; > > while rowcount < max_rows LOOP --of course, could > have just said 60 > > fetch getrow into point_row; > rowcount := rowcount + 1; > > select into v_nearest_line_id > twothousandlines.gid > from twothousandlines > order by st_distance(twothousandlines.geom,point_row.geom) > limit 1; > > insert into linespoints values (point_row.gid, > v_nearest_line_id ); > > end loop; > close getrow; > return(rowcount); > > end; > > $$ language plpgsql > > */ > > > Then ran it: > > /* > select insert_into_linespoints() --389ms > */ > > Then made lines from info in the three tables: > > /* > select > st_makeline(sixtypoints.geom,st_line_interpolate_point(st_geom > etryn(twothousandlines.geom,1),st_line_locate_point(st_geometr > yn(twothousandlines.geom,1),sixtypoints.geom))) > from twothousandlines, sixtypoints, linespoints > where sixtypoints.gid = linespoints.point_id > and twothousandlines.gid = linespoints.nearest_line_id > */ > --12ms > > > In retrospect, I probably would add a geometry column to the > "linespoints" table and insert the line during the function > rather than having the third step. I didn't understand why > you needed to "explode" to linestrings as you called it but I > guess it has to do with your data. The "order by > st_distance() ... limit 1" is what you needed mainly I think > to give you each single nearest line. > > > Hopefully this is what you're trying to do. > > > Eric > > > > Eric Randall > > -Original Message- > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net]on > Behalf Of dassouki > Sent: Thursday, October 01, 2009 11:38 AM > To: postgis-users@postgis.refractions.net > Subject: [postgis-users] Slow Postgres Query > > > Hey All, > > As some of you know, I've been trying to connect a point from a point > table to the projecti
Re: [postgis-users] Slow Postgres Query
Cool! I need to rethink the way I do things! -Eric Eric Randall -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net]on Behalf Of Sufficool, Stanley Sent: Friday, October 02, 2009 3:32 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] Slow Postgres Query I usually avoid cursors when possible: insert into linespoints (point_id, nearest_line_id) select gid, ( SELECT gid from twothousandlines order by st_distance(twothousandlines.geom,sixtypoints.geom) limit 1 ) from sixtypoints > -Original Message- > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On > Behalf Of Randall, Eric > Sent: Friday, October 02, 2009 10:20 AM > To: PostGIS Users Discussion > Subject: Re: [postgis-users] Slow Postgres Query > > > Hi there dassouki, > > Right, no way that should take so long. I created 60 point > and 2000 line tables from addresses and road centerlines to > do a test. The tables are called "sixtypoints" and > "twothousandlines". Then created a "linespoints" table to > store point and nearest line ids: > > /* > create table linespoints ( > point_id integer, > nearest_line_id integer ); > */ > > Then created a function to insert the point and nearest line ids: > > /* > create or replace function insert_into_linespoints() returns > integer as $$ > > declare > > getrow cursor for select * from sixtypoints; > > point_row sixtypoints%rowtype; > > v_nearest_line_id integer; > max_rows integer; > rowcount integer; > > begin > > delete from linespoints; > > select into max_rows count(*) from sixtypoints; > > open getrow; > rowcount := 0; > > while rowcount < max_rows LOOP --of course, could > have just said 60 > > fetch getrow into point_row; > rowcount := rowcount + 1; > > select into v_nearest_line_id > twothousandlines.gid > from twothousandlines > order by st_distance(twothousandlines.geom,point_row.geom) > limit 1; > > insert into linespoints values (point_row.gid, > v_nearest_line_id ); > > end loop; > close getrow; > return(rowcount); > > end; > > $$ language plpgsql > > */ > > > Then ran it: > > /* > select insert_into_linespoints() --389ms > */ > > Then made lines from info in the three tables: > > /* > select > st_makeline(sixtypoints.geom,st_line_interpolate_point(st_geom > etryn(twothousandlines.geom,1),st_line_locate_point(st_geometr > yn(twothousandlines.geom,1),sixtypoints.geom))) > from twothousandlines, sixtypoints, linespoints > where sixtypoints.gid = linespoints.point_id > and twothousandlines.gid = linespoints.nearest_line_id > */ > --12ms > > > In retrospect, I probably would add a geometry column to the > "linespoints" table and insert the line during the function > rather than having the third step. I didn't understand why > you needed to "explode" to linestrings as you called it but I > guess it has to do with your data. The "order by > st_distance() ... limit 1" is what you needed mainly I think > to give you each single nearest line. > > > Hopefully this is what you're trying to do. > > > Eric > > > > Eric Randall > > -Original Message- > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net]on > Behalf Of dassouki > Sent: Thursday, October 01, 2009 11:38 AM > To: postgis-users@postgis.refractions.net > Subject: [postgis-users] Slow Postgres Query > > > Hey All, > > As some of you know, I've been trying to connect a point from a point > table to the projection of that point on the nearest line for > close to > forever now. > > I've managed to get the code working for a small set of points and > lines; however, on a 60 node with 2,000 lines, the query is > in its 11th > hour and still no sign of ending. > > I posted a question including code on stack overflow > http://stackoverflow.com/questions/1504353/slow-postgres-query > > I was wondering if anyone here has any idea on what I might be doing > wrong ? and if there is a way to make the code more efficient > > &
Re: [postgis-users] Slow Postgres Query
Hi there dassouki, Right, no way that should take so long. I created 60 point and 2000 line tables from addresses and road centerlines to do a test. The tables are called "sixtypoints" and "twothousandlines". Then created a "linespoints" table to store point and nearest line ids: /* create table linespoints ( point_id integer, nearest_line_id integer ); */ Then created a function to insert the point and nearest line ids: /* create or replace function insert_into_linespoints() returns integer as $$ declare getrow cursor for select * from sixtypoints; point_row sixtypoints%rowtype; v_nearest_line_id integer; max_rows integer; rowcount integer; begin delete from linespoints; select into max_rows count(*) from sixtypoints; open getrow; rowcount := 0; while rowcount < max_rows LOOP --of course, could have just said 60 fetch getrow into point_row; rowcount := rowcount + 1; select into v_nearest_line_id twothousandlines.gid from twothousandlines order by st_distance(twothousandlines.geom,point_row.geom) limit 1; insert into linespoints values (point_row.gid, v_nearest_line_id ); end loop; close getrow; return(rowcount); end; $$ language plpgsql */ Then ran it: /* select insert_into_linespoints() --389ms */ Then made lines from info in the three tables: /* select st_makeline(sixtypoints.geom,st_line_interpolate_point(st_geometryn(twothousandlines.geom,1),st_line_locate_point(st_geometryn(twothousandlines.geom,1),sixtypoints.geom))) from twothousandlines, sixtypoints, linespoints where sixtypoints.gid = linespoints.point_id and twothousandlines.gid = linespoints.nearest_line_id */ --12ms In retrospect, I probably would add a geometry column to the "linespoints" table and insert the line during the function rather than having the third step. I didn't understand why you needed to "explode" to linestrings as you called it but I guess it has to do with your data. The "order by st_distance() ... limit 1" is what you needed mainly I think to give you each single nearest line. Hopefully this is what you're trying to do. Eric Eric Randall -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net]on Behalf Of dassouki Sent: Thursday, October 01, 2009 11:38 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] Slow Postgres Query Hey All, As some of you know, I've been trying to connect a point from a point table to the projection of that point on the nearest line for close to forever now. I've managed to get the code working for a small set of points and lines; however, on a 60 node with 2,000 lines, the query is in its 11th hour and still no sign of ending. I posted a question including code on stack overflow http://stackoverflow.com/questions/1504353/slow-postgres-query I was wondering if anyone here has any idea on what I might be doing wrong ? and if there is a way to make the code more efficient cheers, dassouki ___ 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] to run a xxx.sql by run a xxx.bat file
What about? C:\Programme\PostgreSQL\8.3\bin\psql -f fog_of_war.sql -d postgis -h localhost -p 5432 postgres postgres Eric -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net]on Behalf Of Daniel Grum Sent: Thursday, September 03, 2009 9:16 AM To: PostGIS Mailing-List Subject: [postgis-users] to run a xxx.sql by run a xxx.bat file Hi, Has anybody worked with a BAT-File ??? I want to run SQL-file with a BAT-file. My BAT file es this text: C:\Programme\PostgreSQL\8.3\bin\psql -h localhost -p 5432 postgres postgres < fog_of_war.sql And PSQL load this but he has a problem because the data is not in der DB postgres, it is in the DB postgis. And so he says: "ERROR ...Relation don't exists" If I insert the information "-d postgis"--> C:\Programme\PostgreSQL\8.3\bin\psql -h localhost -p 5432 postgres postgres -d postgis < fog_of_war.sql PSQL ignores this information and says: "Kommandozeilenargument -d ignoriert" und "Kommandozeilenargument postgis ignoriert" What can be the problem THX --daniel ___ 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] How do you obtaining a WGS-84 (4326) coordinate from a coordinate that has no SRID
Rick wrote: On Wed, Aug 19, 2009 at 5:53 PM, Eric B. Powell mailto:ebpowell.c...@gmail.com>> wrote: ILAN BENISTY wrote: Hi, I am dealing with a problem where I am importing an AutoCAD description of an airport (DXF) into PostGIS. I have a Python script which will import each coordinate to a PostGIS geometry with SRID = -1 (since I do not have real world coordinates, but Cartesian coordinates from a point of origin). I do, however, have a lat/long reference point, which represents the origin of my x/y CAD coordinates. I would like to be able to store my CAD coordinates points as Long/Lat (SRID=4326). Any suggestions? Thanks, Ilan ___ postgis-users mailing list postgis-users@postgis.refractions.net <mailto:postgis-users@postgis.refractions.net> http://postgis.refractions.net/mailman/listinfo/postgis-users I'll answer for Ilan, we work together What country is the site in? What units are used for the drawings grid? Do you know the geographic datum? Country: Canada, Units: meters, Datum: WGS84 I expect the solution is straightforward, but it is not obvious to us. It may require a fair amount of pre-processign before the data can be accurately (de)projected to LatLong. Once the details of the drawing grid are known, there is a python wrapper for Proj4 which could be used to (de)project the points on the way into the database. We are very experienced programmers, but have a limited knowledge of SQL. We've been working with it for a few years, but at this point have been able to get by quite nicely with the basics. We have a fair knowledge of Geodesy. It was my major, 20 years ago. -- I'll give you the use case: Currently we have a geometric database using PostgreSQL geometry types to represent aerodromes. The origin of the grid is the centroid of the aerodrome, and that we have a Geodetic position for. We use it, amoung other things, to plot radar targets. The radar data arrives on the same grid, so up to this point our data registers, we display the live radar data as SVG in any browser that is not IE with one second updates. This works well. However, for many reasons, we have to go to a geodetic system. We'll still use the same metric grid with an origin at the centroid for radar data, but to integrate with other systems we must maintain a geodetic database. The aerodrome data arrives as DXF. We have written an import filter in Python that parses in the DXF and converts it to Postgresql geometry. It also maps the layer names to a standard. Ilan, the author of this thread, is tasked with converting this to PostGIS so that we may merge with other systems that maintain geometry in WGS84. The general coverage of the data is three or four kilometers. Surface radar data arrives at a resolution of one meter, so if our accuracy is within 10cm we are more than good. We assume a reasonably accurate centroid position and a Cartesian plane that does not extend more than 3 kilometers in any direction from the origin, so curvature error is not an issue. -- The problem: We have a metric cartesian data set. We have a geodetic coordinate for the centroid of the aerodrome, which is the origin of our metric Cartesian data. We need to convert this to geodetic to exchange data with other components of the air navigation system. -- It seems to me that the solution might be to have PostGIS give us a coordinate for the centroid, transform the DXF coordinates during the parse operation, and then import it into PostGIS. We don't know how to properly do that, and it's quite possible that that is not the ideal solution. As I said, we are new to PostGIS, but we are keen to play. Eric ___ postgis-users mailing list postgis-users@postgis.refractions.net <mailto:postgis-users@postgis.refractions.net> http://postgis.refractions.net/mailman/listinfo/postgis-users -- Cheers! Rick ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users I was thinking that, if you know the actual location of your points in a projected coordinate system, (de)projecting them to LatLong on the way into PostGIS could be done using the pyProj library which wraps Proj4 in a python wrapper. As such, as the points are read in, re-project them and write them in using st_geometryfromtext(). I have an example of this
Re: [postgis-users] How do you obtaining a WGS-84 (4326) coordinate from a coordinate that has no SRID
ILAN BENISTY wrote: Hi, I am dealing with a problem where I am importing an AutoCAD description of an airport (DXF) into PostGIS. I have a Python script which will import each coordinate to a PostGIS geometry with SRID = -1 (since I do not have real world coordinates, but Cartesian coordinates from a point of origin). I do, however, have a lat/long reference point, which represents the origin of my x/y CAD coordinates. I would like to be able to store my CAD coordinates points as Long/Lat (SRID=4326). Any suggestions? Thanks, Ilan ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users What country is the site in? What units are used for the drawings grid? Do you know the geographic datum? It may require a fair amount of pre-processign before the data can be accurately (de)projected to LatLong. Once the details of the drawing grid are known, there is a python wrapper for Proj4 which could be used to (de)project the points on the way into the database. Eric ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
RE: [postgis-users] PostGIS view in qgis
Try OpenJump with the dbquery plugin. You don't even need the view. -Eric -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net]on Behalf Of Martin Fafard Sent: Monday, March 09, 2009 11:11 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] PostGIS view in qgis Eduin Thank you for the answer but it doesn't work. Since I create new objects from a view (polygonize), it seem to be impossible to create a unique id in this view for these objects. Someone else have ever aggregate geom in a view to see it in qgis or other viewer?? Martin F Eduin Yezid Carrillo Vega a écrit : If your postgresql version is 8.0 or less you can use OID fields as key identifier. CREATE VIEW myview AS SELECT oid AS gid, the_geom FROM mytable; In postgresql 8.3 you can force oid creation if WITH OIDS is specified when the table is created Eduin Yesid Carrillo Vega Ingeniero Civil, Esp. Sistemas de Información Geográfica Centro de Investigación y Desarrollo en Información Geográfica Instituto Geográfico Agustín Codazzi - IGAC Cr 30 48-51 Tel +57 1 3694000 ext 4593 Bogotá D.C., Colombia On Mon, Mar 9, 2009 at 2:43 AM, Marc Jansen <mailto:jansen.m...@gmx.de> wrote: HI Martin, Martin Fafard schrieb: Hi I polygonize linestrings in a view to see the result in qgis. But qgis need gid. How can I create the gid column after an aggregate function in a view? AFAIK you can't. Maybe a table (created by your query) with a serial / primary key helps. --Marc Martin F ___ 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 _ No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.0.237 / Virus Database: 270.11.9/1990 - Release Date: 03/08/09 17:17:00 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
RE: [postgis-users] order of points in ST_ConvexHull
The closest points in your polygon to the respective corners of it's
ST_Envelope() are your polygons corresponding corners.
-Eric
-Original Message-
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net]on Behalf Of Armin
Burger
Sent: Thursday, February 26, 2009 2:57 PM
To: PostGIS Users Discussion
Subject: [postgis-users] order of points in ST_ConvexHull
Hello
I need to find a possibility to identify upper-left, upper-right, etc.
corners of +/- rectangular polygons. I.e. polygons with guranteed just 4
corners, but with a shape that is typically between a rectangle and a
rhomb. The polygons define the geometry of image boundaries ("image
footprints"). But it cannot be guaranteed which point in the polygon
corresponds to which corner since the order of points during geometry
creation is unknown.
One idea was to use ST_ConvexHull(geometry) since for this very simple
polygons the convex hull seems to be identical with the geometry. It
looked to me that the order in this convex hull was:
lower-right, lower-left, upper-left, upper-right
Does anybody know if this order is always like that or can this order
change? Would anybody know another method to identify which point of the
polygon corresponds to which corner?
Regards
Armin
___
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] What SQL admin tools are you using?
Stefan,
Thanks for the info, I'll definitely give it a try. I mostly use pgAdmin3 and
OpenJump but I also use Lightning Admin for PG and MySQL.
http://www.amsoftwaredesign.com/
Not free but cheap when on sale (currently 4.99 for both until tomorrow).
-Eric
-Original Message-
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net]on Behalf Of
Stefan Keller
Sent: Wednesday, December 31, 2008 1:13 PM
To: PostGIS Users Discussion
Subject: [postgis-users] What SQL admin tools are you using?
Regaring surveys... I have yet another question:
What PostGIS/PostgreSQL admin tools are you using?
I think up to now there have been three possibilities:
1. GIS built in (GUI)
2. psql (CLI)
3. pgAdminIII (GUI)
Recently I've found a nice alternative to 3. pgAdminIII, called:
4. "SQL Workbench/J" (www.sql-workbench.net).
Stefan
NOTE: Download SQL Workbench/J development build 102.8 (2008-12-31) or
later! In order to display geometry data as text (i.e. as WKT) in
SELECT statements, follow the following instructions (from:
http://www.sql-workbench.net/dev-history.txt > Build 102.8,
31.12.2008)
> Enh: (102.7) for specific datatypes, data retrieval in the DbExplorer can now
> be done by applying expressions to the column when building the SQL
> to select the data.
So, for every DB there are configurations entries in a file called
"workbench.settings" (Windows users look at "C:\Documents and
Settings\\.sqlworkbench\"). There, the following line has to be
inserted:
workbench.db.postgres.selectexpression.geometry=astext(transform(${column},4326))
Now, for SELECT statements in the DbExplorer view of SQL Workbench/J
all column values of type geometry get through this function.
___
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] The Old Who is using PostGIS survey again?
1) How you use PostGIS? a.) Monthly transfer of County ESRI SDE/MSSQL Geodatabase to County PostGIS Database b.) from which many manipulations, extractions, calculations, and updates are performed with greater ease and automation than from the ArcGIS tools. Some of our uses include: c.) PA Act 319 Clean and Green calculations d.) Pennsylvania Agricultural Conservation Easement Application Scoring e.) Monthly Municipal GIS Extraction for the Municipalities f.) Datastore for intranet web mapping. g.) Virtually any ad hoc analysis and querying. h.) Explaining GIS. 2.) What you find useful about it over anything else? a.) Probably one of the most useful aspects of PostGIS for me personally has been it's instructiveness (is that a word?). Between the database/SQL nature of interfacing with spatial/non data, and the great community surrounding it, I think it is the ideal GIS learning environment. I think of it as a GIS Erector Set. I'm suprised that most Universities don't seem to be using it for teaching (This alone I think would be a great reason for a book). b.) The variety of ways that solutions can be thought through and worked through. Usually straightforward, and when not, there is always plenty of help. c.) Portable, numerous OSs and architectures. d.) It JUST doesn't SUCK! (you may remove that) 3.) Why you think there should be any book written focused on its use and of course if such a thing were to exist, would you buy it? See 1h. and 2a., and yes, I will buy one...I'll always know someone who can use it. -Eric Eric Randall GIS DB Admin/Analyst County of Erie 140 W 6th St Room 119 Erie, PA 16501 ph. 814-451-6063 fx. 814-451-7000 -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net]on Behalf Of Paragon Corporation Sent: Tuesday, December 30, 2008 7:22 AM To: 'PostGIS Users Discussion' Subject: [postgis-users] The Old Who is using PostGIS survey again? Leo and I have a good portion of a PostGIS book written. Ideally we would like to get a publisher to publish it, but seem to be running into the same obstacles. No publisher seems to think people use PostGIS to warrant enough demand for a book dedicated to it. So to make a decent case for a book, can each of you in your own words describe 1) How you use PostGIS? 2) What you find useful about it over anything else? 3) Why you think there should be any book written focused on its use and of course if such a thing were to exist, would you buy it? Of course I'll also need some official download stats etc. which hopefully the Refractions group can help out with. I think Mark Cave-Ayland had posted some stats a while back, but can't find them. Thanks, Regina ___ 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] Dividing an area to closest line
If I understand correctly I think that you want to use the method Regina is suggesting to group points nearest your line parts and then use ST_collect and ST_convexhull to make polygons out of your point groups. -Eric -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net]on Behalf Of nicklas.a...@jordogskog.no Sent: Monday, December 29, 2008 7:28 AM To: PostGIS Users Discussion Subject: RE: [postgis-users] Dividing an area to closest line Thanks for the answer. My problem is that I just have the roadparts and nothing to relate them to. I would like to create polygons with their borders between the roads so every possible point inside the polygon share the same closest road. But if it isnt possible I will have to build a raster of rectangels or just points and use the method you suggested on them. /Nicklas 2008-12-29 Obe Regina wrote: > Why wouldn't you use a nearest neighbor calculation as demonstrated here (using DISTINCT ON)? > > http://postgis.refractions.net/documentation/manual-svn/ST_DWithin.html > > Keep in mind ST_DWithin and ST_Distance don't care about whether a geometry is a line or polygon or point or collection or whatever. They will always consider the minimum distance between 2 geometries. > > ST_DWithin will use a spatial index where as ST_Distance will not. So for ST_DWithin to work, you have to make the assumption, > the closest road to any point is no more than X units away. The bigger you make the X, the less efficient the index search, but the less likely you will leave a point out. > > Hope that helps, > Regina >> _ From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of nicklas.a...@jordogskog.no >Sent: Monday, December 29, 2008 3:18 AM >To: postgis-users@postgis.refractions.net >Subject: [postgis-users] Dividing an area to closest line > > > > Hallo > > My first time writing here. > > The case is that I need to find the closest road from every point in the terrain. The roads are defined in "roadparts". The very best thing would be to get polygons around every roadpart which I could use on other data to bind it to the right road. > > The only solution I can see is making a raster of small rektangels that I bind to my roadparts using nearest neighbour-calculations. > > My question is if someone has a more efficient and better solution. > > Sorry for my bad english > > Thanks > /Nicklas Avén ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Exploding Polygons
Hi all, I'm trying to figure out how to explode a set of polygons to display them with space between. In my case they are municipal boundary polys but could be anything. So far the only approach I can think of is using translate such that: select st_translate(st_translate(poly, -x(st_centroid(poly)), -y(st_centroid(poly))),x(p2) + (x(p2)- x(p1)), y(p2) + (y(p2) - y(p1))) from (select st_centroid(st_collect(geom))as p1 from mypolytable) as s1, (select st_centroid(geom) as p2, geom as poly from mypolytable) as s2 The problem with this is that small polys (Boroughs, etc) relative larger ones often don't get their own space until a large enough multiplier is applied, i.e. x(p2) + (x(p2)- x(p1))*5, y(p2) + (y(p2) - y(p1))*5 , but then there is too much space around the larger polys. Anyone have a suggestion or done this another way? Thanks. think i'm gonna splode..., Eric ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
RE: [postgis-users] Arc catalog (Arc GIS) import
Bruce, Not much. I probably should have said "less possible". I'm not very ArcObjects experienced so while I'm sure anything is possible, for me the first reliable solution tends to get used and the better solutions exist in the "time permitting" continuum. -Eric -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Clay, Bruce Sent: Wednesday, November 26, 2008 10:26 AM To: PostGIS Users Discussion Subject: RE: [postgis-users] Arc catalog (Arc GIS) import Eric: You said "not possible with ArcObjects". Just out of curiosity have you tried a mixture of ArcObjects and GDAL/OGR? I have that on my list of things to do but I don't want to go down that path if it has been tried and doesn't work. Bruce -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Randall, Eric Sent: Wednesday, November 26, 2008 9:55 AM To: PostGIS Users Discussion Subject: RE: [postgis-users] Arc catalog (Arc GIS) import Hi John, You could create a custom menu/toolbar in ArcMap or ArcCatalogue and then using VBA: 1. use the "Shell" command to pass pgsql2shp (or ogr2ogr) command and it's arguments to the database (or .bat doing the same). 2. add the shape(s) to your project (or "Refresh" if already there). It's not very beautiful but I use this method to pass arguments from ArcMap to PostGIS functions to do their work and then write updated shapes back out. A pretty "loose coupling" to PostGIS admittedly. Two reasons I do this are: 1. To do things that are easier, less agravating, or not possible (for me anyway) with Modelbuilder,Toolbox,ArcObjects. 2. If you need to showcase some PostGIS functionality and people don't see an ESRI frontend, they tend to look at you like you have three heads. I look forward to using ZigGIS. That will eliminate the shape writing stuff. That's still a month away though (new year, new budget). -Eric Eric Randall GIS DB Admin/Analyst County of Erie 140 W 6th St Room 119 Erie, PA 16501 ph. 814-451-6063 fx. 814-451-7000 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of John Abraham Sent: Tuesday, November 25, 2008 7:00 PM To: postgis-users@postgis.refractions.net Subject: [postgis-users] Arc catalog (Arc GIS) import A colleague told me that ArcGIS could import postgis layers directly, without using Ziggis. I've been googling around and playing with ArcMAP and ArcCatalog trying to figure it out, but no luck so far. I know I could use pgsql2shp. And I know I could use Ziggis commercial (2.x) or Ziggis open source (1.x). But for our needs a direct import menu item within ArcMAP or ArcCatalog would be best, as they would like to frequently view snapshots of the data. Any other suggestions also appreciated, thanks. -- John Abraham [EMAIL PROTECTED] ___ 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 This message and any enclosures are intended only for the addressee. Please notify the sender by email if you are not the intended recipient. If you are not the intended recipient, you may not use, copy, disclose, or distribute this message or its contents or enclosures to any other person and any such actions may be unlawful. Ball reserves the right to monitor and review all messages and enclosures sent to or from this email address. ___ 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] Arc catalog (Arc GIS) import
Hi John, You could create a custom menu/toolbar in ArcMap or ArcCatalogue and then using VBA: 1. use the "Shell" command to pass pgsql2shp (or ogr2ogr) command and it's arguments to the database (or .bat doing the same). 2. add the shape(s) to your project (or "Refresh" if already there). It's not very beautiful but I use this method to pass arguments from ArcMap to PostGIS functions to do their work and then write updated shapes back out. A pretty "loose coupling" to PostGIS admittedly. Two reasons I do this are: 1. To do things that are easier, less agravating, or not possible (for me anyway) with Modelbuilder,Toolbox,ArcObjects. 2. If you need to showcase some PostGIS functionality and people don't see an ESRI frontend, they tend to look at you like you have three heads. I look forward to using ZigGIS. That will eliminate the shape writing stuff. That's still a month away though (new year, new budget). -Eric Eric Randall GIS DB Admin/Analyst County of Erie 140 W 6th St Room 119 Erie, PA 16501 ph. 814-451-6063 fx. 814-451-7000 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of John Abraham Sent: Tuesday, November 25, 2008 7:00 PM To: postgis-users@postgis.refractions.net Subject: [postgis-users] Arc catalog (Arc GIS) import A colleague told me that ArcGIS could import postgis layers directly, without using Ziggis. I've been googling around and playing with ArcMAP and ArcCatalog trying to figure it out, but no luck so far. I know I could use pgsql2shp. And I know I could use Ziggis commercial (2.x) or Ziggis open source (1.x). But for our needs a direct import menu item within ArcMAP or ArcCatalog would be best, as they would like to frequently view snapshots of the data. Any other suggestions also appreciated, thanks. -- John Abraham [EMAIL PROTECTED] ___ 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] how to connect ArcGIS 9.3 to PostgiswithoutArcSDE ?
What about the ZigGIS extension? http://pub.obtusesoft.com/ I don't have it yet but am planning on acquiring a couple liceses in order to use PostGIS data from the ArcGIS Desktop. That might be the lowest cost solution without changing clients. -Eric -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Paragon Corporation Sent: Friday, November 21, 2008 4:02 AM To: 'PostGIS Users Discussion' Subject: RE: [postgis-users] how to connect ArcGIS 9.3 to PostgiswithoutArcSDE ? Not sure if anyone mentioned uDig and OpenJump already. Those you can edit with as well I believe, though I don't really do much editing with tools so can't speak for the merits of editing. My personal favorite is OpenJump. Manifold GIS by the way has a lot of charm. I haven't really dug that deeply into my copy yet, but there is a lot under the hood there. And if per chance you are dealing with multiple different spatial databases it supports SQL Server 2008, PostGIS, DBII I think, and Oracle Spatial some really nifty looking RASTER functions without any SDE goo. I find it harder to get into though than something like OpenJump without reading a bit. Granted SDE does afford some things like hmm versioning and RASTER storage? And you can use PostGIS raw without going thru the SDE service as long as you pay for the SDE service I think. Though the idea that you have to pay for something so that you can not use it seems a little strange. Someone correct me if I am wrong in my impression there. It seems if you have the service turned off and you can still connect via ArcGIS, doesn't that mean you didn't need the service, but you have to still pay for the service? Thanks, Regina -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Simon Greener Sent: Friday, November 21, 2008 3:39 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] how to connect ArcGIS 9.3 to Postgis withoutArcSDE ? The problem is that John has bought (or has been given to use) the "Rolls Royce" GIS. Or, to be more fair, has paid $$ for a commercial GIS client (one amongst many) with all its (hidden) limitations and issues. John wants the freedom to decide how to connect to data sources using his client of choice. A reasonable thing to ask. But, for those of use who know how the "ESRI Soviet" works, such freedom isn't available to those who own one of their licenses: he has to pay for an ArcSDE license (and, probably, be told that the new all-singing-all-dancing-ESRI-spatial-type for PostgreSQL is best. "but we do support PostGIS if you must"). Suggesting free software, like Quantum GIS etc, requires a huge shift in mindset that ESRIlites (or anyone who has spent money on a commercial GIS) finds hard to do. To those who say Quantum GIS, I would say Manifold GIS. It will do what John wants, but he is as likely to change to Manifold GIS as he is to Quantum GIS. Cynically and sadly, Simon On Wed, 19 Nov 2008 19:10:41 +1100, Ervin Ramonllari <[EMAIL PROTECTED]> wrote: > > Hi, > > I would suggest Quantum GIS, open source GIS software, have a look at > http://www.qgis.org > > Ervin > > Date: Tue, 18 Nov 2008 12:11:18 -0800 > From: [EMAIL PROTECTED] > To: postgis-users@postgis.refractions.net > Subject: [postgis-users] how to connect ArcGIS 9.3 to Postgis without ArcSDE ? > > Hi there, > > I was wondering if there is a way to connect to Postgis for ArcGIS 9.3. We are planning to manage spatial data by postgres. However, how can we view/edit the data by ArcGIS? > > Any input would be appreciated > > > Thanks a lot > John > > _ > Discover the new Windows Vista > http://search.msn.com/results.aspx?q=windows+vista&mkt=en-US&form=QBRE -- SpatialDB Advice and Design, Solutions Architecture and Programming, Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, Radius Topology and Studio Specialist. 39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia. Website: www.spatialdbadvisor.com Email: [EMAIL PROTECTED] Voice: +613 9016 3910 Mobile: +61 418 396391 Skype: sggreener Longitude: 147.20515 (147° 12' 18" E) Latitude: -43.01530 (43° 00' 55" S) NAC:W80CK 7SWP3 ___ 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] LiDAR data in PostGIS
Hi All, Who is using LiDAR data in PostGIS? Can you comment on your experiences? I'm still awaiting delivery for my area but meanwhile have started looking at a sample from another county. I'm interested in how people are working with it and what you are finding are manageable subsets of the data. I'm experimenting with indexing it using various sized square and hexagon tilings but haven't done too much else. Thanks for comments. -Eric ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
RE: [postgis-users] PostGis on an existing Postgres installation
oops, also if you don't already you need to have plpgsql language support installed before running lwpostgis.sql using createlang plpgsql yourexistingdb -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Randall, Eric Sent: Sunday, October 26, 2008 6:54 AM To: PostGIS Users Discussion Subject: RE: [postgis-users] PostGis on an existing Postgres installation James, You need only run the lwpostgis.sql script in (/share/contrib) against your exiting database to spatially enable it. Also run spatial_ref_sys.sql Eric -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of James Cauchi Sent: Sunday, October 26, 2008 5:32 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] PostGis on an existing Postgres installation Hi PostGis Community, My Question: Does everyone have a separate PostGis and Postgres db in their server or do people put all their data in the PostGis db? Background: I have been using Postgres DB since September and just now I need to add GIS support to my application so the obvious thing was to install PostGis. During my PostGis installation a new db (called PostGis) was created. I need to link my goereferenced data to other data in my db before i display it to the user on the map and that is why i need all the data in 1 db. cross-db references are not allowed in Postgres so I am left with 2 options: 1. Copy all my existing data to the PostGis db 2. Copy the whole PostGis db into my existing db. Both these options seem cumbersome and pretty silly so I must be missing something here - please enlighten me. thanks 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
RE: [postgis-users] PostGis on an existing Postgres installation
James, You need only run the lwpostgis.sql script in (/share/contrib) against your exiting database to spatially enable it. Also run spatial_ref_sys.sql Eric -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of James Cauchi Sent: Sunday, October 26, 2008 5:32 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] PostGis on an existing Postgres installation Hi PostGis Community, My Question: Does everyone have a separate PostGis and Postgres db in their server or do people put all their data in the PostGis db? Background: I have been using Postgres DB since September and just now I need to add GIS support to my application so the obvious thing was to install PostGis. During my PostGis installation a new db (called PostGis) was created. I need to link my goereferenced data to other data in my db before i display it to the user on the map and that is why i need all the data in 1 db. cross-db references are not allowed in Postgres so I am left with 2 options: 1. Copy all my existing data to the PostGis db 2. Copy the whole PostGis db into my existing db. Both these options seem cumbersome and pretty silly so I must be missing something here - please enlighten me. thanks 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
RE: [postgis-users] Loading multiple shapefiles with differentschemas into the same PostGIS layer
Why not just bring everything in with shp2pgsql and then a bunch of insert into...select...from... -Eric -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Stefan Keller Sent: Saturday, October 18, 2008 8:10 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] Loading multiple shapefiles with differentschemas into the same PostGIS layer If table structures (and even formats) are different and you want to automate it without programming, I would recommend an upcoming ETL tool called GeoKettle from Univ. Laval, Canada: http://tinyurl.com/43w5en -- Stefan 2008/10/17 Todd Fagin <[EMAIL PROTECTED]>: > Greetings PostGIS-ers, > > > > I have another newbie question for you. > > > > I have multiple shapefiles I want to load into PostGIS as a single layer. > However, these shapefiles have different schemas. I know that shp2pgsql has > the -a option for appending However, the tables must have the same schema, > right? > > > > I have come up with a convoluted way to do this and it works. However, I > have feeling that someone much smarter than I has already found a more > elegant solution to this. > > > > Here is what I have done: > > > > 1. Convert each shapefile to SQL using shp2pgsql; this is especially > important since it creates the the_geom field > > 2. Run the SQL script for the shapefile that has the schema I will use > > 3. Edit the remaining .sql files generated for each shapefile by shp2pgsql, > removing everything but the INSERT statements and editing the fields to map > to the appropriate fields in the existing table (this is the part that could > be cumbersome, hence looking for a more elegant solution). > > 4. Run the edited SQL scripts for each of the remaining shapefiles. > > > > Thanks, > > > > Todd > > > > Todd Fagin > > > > Coordinate Solutions, Inc. > > 2804 NW 18th St. > > Oklahoma City, OK 73107 > > 405.740.4324 (voice) > > 904.471.5548 (fax) > > www.coordinatesolutions.com > > > > > > > > ___ > 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] ESRI 9.3 & PostGIS
Brent, You've probably seen this already but there is a series of articles that talk some about that: http://geobabble.wordpress.com/2008/05/28/using-arcsde-93-with-postgresql-part-1/ http://geobabble.wordpress.com/2008/06/02/using-arcsde-93-with-postgresql-part-2/ http://geobabble.wordpress.com/2008/07/01/using-arcsde-93-with-postgresql-part-3/ http://geobabble.wordpress.com/2008/08/21/using-arcsde-93-with-postgresql-part-35/ Pretty interesting. Eric -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Brent Wood Sent: Wednesday, October 08, 2008 4:29 PM To: PostGIS Users Discussion Subject: [postgis-users] ESRI 9.3 & PostGIS The institute I work for is a long term ESRI user, while I've been encouraging the use of FOSS GIS solutions for some years, with growing success. We are looking at replacing our Arc/Oracle setup with Arc/PostGIS when we upgrade to 9.3 and I'm interested in any experiences anyone may have with this. We are hoping that an Arc PostGIS geodtabase can be managed via Arc, but have the contents available as normal (read-only) PostGIS tables, etc via PostGIS client apps such as Geoserver, Mapserver, uDIG, QGIS, gvSIG, R-GDAL, etc. Has anyone here had any experience with PostGIS as a 9.3 Geodatabase and how open the data is in these, or whether the geodatabase is inherently tied up with non-standard structures that don't work well with typical PostGIS clients? Thanks, Brent Wood ___ 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] extending a two point line segment
Silly question (as my kids pointed out). Please disregard. Thanks. Eric -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Randall, Eric Sent: Saturday, September 27, 2008 10:43 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] extending a two point line segment Hi all, I'm trying to extend a two point line segment in order to determine it's intersection point with a second line, kind of like the arcinfo "extend" command. If there is a postgis function or combination of them to do this, I've missed it. Any suggestions are appreciated. Thanks. Eric ___ 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] extending a two point line segment
Hi all, I'm trying to extend a two point line segment in order to determine it's intersection point with a second line, kind of like the arcinfo "extend" command. If there is a postgis function or combination of them to do this, I've missed it. Any suggestions are appreciated. Thanks. Eric ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
RE: [postgis-users] list of the closest borders
Hi Doug,
If your point is in a table points and your states are in a table states and
your distances are in feet
then one way might be something like:
select states.stateabbrev, st_distance(states.geom,points.geom)/5280
from points, states
where points.point_id = your_PA_point_id
and stateabbrev in ('OH','MD',''WV','VA')
order by st_distance(states.geom,points.geom)
If your point is an xy location then similarly (using example point in Greene
County)
select states.stateabbrev,
st_distance(states.geom,makepoint(1263197,192349))/5280
from states
where stateabbrev in ('OH','MD',''WV','VA')
order by st_distance(states.geom,makepoint(1263197,192349))
If you are talking about the SW corner point on the PA Boundary then you'll
need to get the
point on PA Boundary nearest the SW point (always the first point I believe)
on the exteriorring of the envelope of the PA Boundary which would be:
select
st_line_interpolate_point(st_exteriorring(st_geometryn(geom,1)),st_line_locate_point(st_exteriorring(st_geometryn(geom,1)),st_pointn(st_exteriorring(st_envelope(geom)),1)))
from states where stateabbrev = 'PA'
and use that point in the query(s) above. There are other ways, probably
better ways too.
-Eric
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Doug Fischer
Sent: Tuesday, September 09, 2008 10:55 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] list of the closest borders
Sorry but I just noticed that I made an error in my post. What I meant to say
was the SW corner of PA and in the table the distance to PA should be 0 because
the Point is in PA.
Thanks,
Doug
On Sep 9, 2008, at 8:52 AM, Doug Fischer wrote:
I am new to GIS / PostGIS so please forgive me if this is a simple question.
Assuming that I have a database populated with all of the proper data, I need
to do determine a list of the closest borders from a point including distances.
ex.
If I have a point located in the SE corner of PA, I would like to run a query
that will return a list containing something like the following:
Border
Distance in miles
PA
A
OH
B
MD
C
WV
D
VA
E
Is there a function in PostGIS that can do this type of thing? Does anyone have
an example?
Thank you very much.
___
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] Merge all polygons from a dataset
As others have mentioned somewhere, you could also use: select st_buffer(st_collect(the_geom),0) from yourpolytable Eric -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of danny Sent: Friday, August 01, 2008 6:12 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] Merge all polygons from a dataset Hello everybody, I've converted a shapefile of Europe to PostGIS. I would like to merge all the countries together so that I only have one polygon left, the whole contour of Europe. I have tried ST_Union that gives me no result. I've tried several other functions to no avail. Does anybody know how I should proceed? Many thanks, Erik ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
RE: [postgis-users] export table from postgis to arcsde
I use Pentaho Data Integration for something similar, though mostly the other direction. Using sql scripts, shp2pgsql/pgsql2shp, and python scripts generated from modelbuilder/ArcGIS toobox tools, Pentaho is then used as the organizing/scheduling/executing tool. Works nicely. I also use it for PostgreSQL/SQL Server/Oracle transfers and such. Eric -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Paul Ramsey Sent: Wednesday, June 18, 2008 5:37 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] export table from postgis to arcsde If you have control of both the pgsql and the sde, you could use ogr2ogr and a windows scheduler or cron job to fire it off and directly translate from pgsql to sde. P On Wed, Jun 18, 2008 at 8:07 AM, Paragon Corporation <[EMAIL PROTECTED]> wrote: > I use a PostgreSQL pgagent job to do nightly dumps of a PostgreSQL view put > as an ESRI shape file. I have people on the other end that have some > automated routine for importing this shape file into a SQL Server ArcSDE. > I'm not sure how they do that, but I assume there most be some automated > tool they use that loads shapefiles into ArcSDE. > > Hope that helps, > Regina > > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > Benedetto Porfidia > Sent: Tuesday, June 17, 2008 5:49 AM > To: postgis-users@postgis.refractions.net > Subject: [postgis-users] export table from postgis to arcsde > > I have a postgis database and i need to export a view from it to a sde db > that is a source for arcims service. I should update a sde table with the > postgis view. How can I do it? > any hint is appreciated. > Benedetto > ___ > 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] Getting dimensions of polygon sides
Maybe not be on the point of this discussion but as the ArcGIS 9.2 "feature to point, line, and polygon" tools output annotation to shape, feature class, etc., sometimes weirdly, and since the attributes have the text, angle, fontsize, etc., I generally export as point shapefiles, then bring them into PostGIS. QGIS and OpenJump labels reference the font, angle fields so with points set as transparent they look pretty good I think. -Eric -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Bruce Rindahl Sent: Monday, June 16, 2008 12:09 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] Getting dimensions of polygon sides Obe, Regina wrote: When I exported it out with Ogr2Ogr, it exported out polygon boxes angled in the direction the text should be angled (almost like a placeholder for the text). That is how I get it. You can use it in ArcMap but the exported shapefile is exactly as you describe. Bruce So does it look fine in ArcMap, but when you export it out it sometimes has a line crossing itself? or you can't export it out at all? -Original Message- From: [EMAIL PROTECTED] [ mailto:[EMAIL PROTECTED] On Behalf Of Bruce Rindahl Sent: Monday, June 16, 2008 11:08 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] Getting dimensions of polygon sides My limited experience is the annotation in ArcMap can be a pretty bizarre format. Sometimes is comes out fine - other times it has a line crossing itself. If you have a curved annotation, it gets even worse. Bruce Obe, Regina wrote: Can someone who has ArcMap 9.2 verify for me if it can or can not export annotations in ESRI Shapefile format. The idea that it can't is totally baffling to me. So I'm guessing its some misunderstanding. I still have to install my ArcMap 9.2 and become an addict, but I think it would take me a while to get up to speed since all the menu options are too much for my little brain to handle. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users - The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer. ___ 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] Circle
Wouldn't that spawn anti-polygons in parallel universes? :-) (I tried to think of something as clever as the "buttered cat array" but it turns out that I'm not that clever.) Eric -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Bob Pawley Sent: Friday, June 13, 2008 9:29 PM To: [EMAIL PROTECTED]; PostGIS Users Discussion Subject: Re: [postgis-users] Circle So if I had a geometry (or collection of geometries) a buffer could be established around those geometries such that they could be isolated from further intrusion?? If sufficent buffers could be established around inhabited space only empty, or near empty, space would remain? In fact, a roving buffer could be established around empty space(s) to continually identify those empty spaces??? Bob - Original Message - From: <[EMAIL PROTECTED]> To: "PostGIS Users Discussion" Sent: Friday, June 13, 2008 5:05 PM Subject: Re: [postgis-users] Circle > See ST_buffer at http://postgis.refractions.net/docs/ch06.html#id2594743 > > > Buffer generates a buffer zone around an existing geometry a specified > distance out (or in if -ve) using a specified number of points. A buffer > around a point is a circle if you use enough points, if you could buffer a > point with 4 points, you'd get a square centred on the point instead of a > circle. > > > Cheers > > Brent Wood > > > --- On Sat, 6/14/08, Bob Pawley <[EMAIL PROTECTED]> wrote: > >> From: Bob Pawley <[EMAIL PROTECTED]> >> Subject: Re: [postgis-users] Circle >> To: "PostGIS Users Discussion" >> Date: Saturday, June 14, 2008, 11:54 AM >> Is that the only function of the buffer - to create a >> circle? >> >> Can the buffer modify other functions. If so - what is the >> criteria? >> >> Bob >> - Original Message - >> From: "Paul Ramsey" >> <[EMAIL PROTECTED]> >> To: "PostGIS Users Discussion" >> >> Sent: Friday, June 13, 2008 4:33 PM >> Subject: Re: [postgis-users] Circle >> >> >> > Try it and see :) >> > It constructs a circular-shaped polygon around the >> point. >> > P >> > >> > On Fri, Jun 13, 2008 at 4:23 PM, Bob Pawley >> <[EMAIL PROTECTED]> wrote: >> >> I'm interested in what the buffer actually >> does. >> >> >> >> Bob >> >> >> >> - Original Message - From: "Paul >> Ramsey" >> >> <[EMAIL PROTECTED]> >> >> To: "PostGIS Users Discussion" >> >> >> Sent: Friday, June 13, 2008 3:46 PM >> >> Subject: Re: [postgis-users] Circle >> >> >> >> >> >>> ST_Buffer(ST_MakePoint(x,y),r) >> >>> >> >>> On Fri, Jun 13, 2008 at 3:35 PM, Bob Pawley >> <[EMAIL PROTECTED]> wrote: >> >>>> >> >>>> What is the best way of creating a circle? >> >>>> >> >>>> Mu PostgreSQL Developes Handbook susy it >> should be - point and radius. >> >>>> >> >>>> Postgis doesn't seem to have this >> function. >> >>>> >> >>>> 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 >> >> >> >> ___ >> >> 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 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
RE: [postgis-users] Section of Circle from 3 Points and Convert toLinestring
Simon, That's perfect, thank you! -Eric -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Simon Greener Sent: Wednesday, June 11, 2008 9:47 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] Section of Circle from 3 Points and Convert toLinestring Eric, > What a great bunch of GeoSkirmishers you are! > Here is another question. > > I have a line AB where A is (0,0) and B is (10,0). > I also have a point P that is (5,3). > I know that the section of the circle that passes through A,P, and B > can be determined if I know the circle center - point C, which > can be defined as the intersection of perpendicular lines projected > from the midpoints of AP and BP. > > What is the most economical way of expressing this using PostGIS/PostgreSQL > fuctions? > Alternatively, if there is a better approach, what is that? Thanks! I don't know if this function is of use to you: create function FindCircle(p_X1 real, p_Y1 real, p_X2 real, p_Y2 real, p_X3 real, p_Y3 real, OUT p_CX real, OUT p_CY real, OUT p_Radius real) As $$ Declare dA real; dB real; dC real; dD real; dE real; dF real; dG real; BEGIN dA := p_X2 - p_X1; dB := p_Y2 - p_Y1; dC := p_X3 - p_X1; dD := p_Y3 - p_Y1; dE := dA * (p_X1 + p_X2) + dB * (p_Y1 + p_Y2); dF := dC * (p_X1 + p_X3) + dD * (p_Y1 + p_Y3); dG := 2.0 * (dA * (p_Y3 - p_Y2) - dB * (p_X3 - p_X2)); -- If dG is zero then the three points are collinear and no finite-radius -- circle through them exists. If ( dG = 0 ) Then p_Radius := -1; Else p_CX := (dD * dE - dB * dF) / dG; p_CY := (dA * dF - dC * dE) / dG; p_Radius := sqrt(power(p_X1 - p_CX,2) + power(p_Y1 - p_CY,2) ); End If; end; $$ LANGUAGE plpgsql; regards Simon -- SpatialDB Advice and Design, Solutions Architecture and Programming, Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, Radius Topology and Studio Specialist. 39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia. Website: www.spatialdbadvisor.com Email: [EMAIL PROTECTED] Voice: +613 9016 3910 Mobile: +61 418 396391 Skype: sggreener Longitude: 147.20515 (147° 12' 18" E) Latitude: -43.01530 (43° 00' 55" S) NAC:W80CK 7SWP3 ___ 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] Section of Circle from 3 Points and Convert to Linestring
What a great bunch of GeoSkirmishers you are! Here is another question. I have a line AB where A is (0,0) and B is (10,0). I also have a point P that is (5,3). I know that the section of the circle that passes through A,P, and B can be determined if I know the circle center - point C, which can be defined as the intersection of perpendicular lines projected from the midpoints of AP and BP. What is the most economical way of expressing this using PostGIS/PostgreSQL fuctions? Alternatively, if there is a better approach, what is that? Thanks! -Eric Eric Randall County of Erie, Pa. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
RE: [postgis-users] Getting dimensions of polygon sides
and of course I forgot to close the cursor, sorry.
Eric
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of
Randall, Eric
Sent: Wednesday, June 11, 2008 3:08 PM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] Getting dimensions of polygon sides
oops... below
"create table parcel_dims (pin text, segment numeric(11,3), length float);"
should be
"create table parcel_dims (pin text, segment integer, length numeric(11,3));
...
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of
Randall, Eric
Sent: Wednesday, June 11, 2008 2:57 PM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] Getting dimensions of polygon sides
Hi Regina,
A very non-elegant try on my parcels.
This just ends up making lines out of every two point segment of a parcel poly
and IDs them in order.
Eric
CREATE OR REPLACE FUNCTION taxparcel_to_segments() RETURNS integer AS $$
DECLARE
getrow CURSOR FOR SELECT * FROM taxparcel;
tp_row taxparcel%ROWTYPE;
vsegment integer;
maxrows integer;
maxpoints integer;
i integer;
j integer;
rowcount integer;
BEGIN
/* select DropGeometryColumn('','parcel_dims','geom');
drop table parcel_dims;
create table parcel_dims (pin text, segment numeric(11,3), length
float);
select
addgeometrycolumn('public','parcel_dims','geom',2271,'LINESTRING',2);*/
TRUNCATE parcel_dims;
SELECT INTO maxrows count(*)FROM taxparcel;
OPEN getrow;
rowcount := 0;
i := 1;
WHILE rowcount < maxrows LOOP -- might want to limit this to 100 to
test
FETCH getrow into tp_row;
rowcount := rowcount + 1;
select into maxpoints ST_npoints(tp_row.geom);
FOR i in 1 .. maxpoints - 1 LOOP
vsegment := i;
j := i + 1;
INSERT INTO parcel_dims VALUES
( tp_row.taxpin, vsegment,
ST_length(ST_makeline(ST_pointn(ST_boundary(tp_row.geom),i),ST_pointn(ST_boundary
(tp_row.geom),j)))::numeric(11,3),
ST_makeline(ST_pointn(ST_boundary(tp_row.geom),i),ST_pointn(boundary(tp_row.geom),j))
);
END LOOP;
END LOOP;
RETURN(rowcount);
END;
$$ language plpgsql
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Obe,
Regina
Sent: Wednesday, June 11, 2008 11:02 AM
To: PostGIS Users Discussion
Subject: [postgis-users] Getting dimensions of polygon sides
I'm trying to similuate ArcGIS annotations. I guess ArcPad doesn't
support ArcGIS annotations according to what I have been told but can
support line strings and so forth.
Here is the the problem. I have a set of parcel polygon geometries.
I'm going to assume that each lines side is composed of 2 points and no
polygon has more than 1000 sides. I need to create a table that has a
separate row for each side with the length of that side as an attribute
fields.
I stupidly thought I could take the boundary and then figure out the
length of the boundary forgetting that this just gives me the perimeter.
So my second thought was that if I reconstitute points of the boundary
grouping 2 at a time - that would do the trick.
My query is still running so haven't looked to see what the final result
is. I'm wondering if someone has done something similar and if they
have an easier way.
Below is the query I am testing right now.
INSERT INTO assessing.parcdimstime_2008(pid, pid_year, the_geom)
SELECT b.pid, b.pid_year, ST_MakeLine(ST_PointN(the_boundary,n),
ST_PointN(the_boundary, n + 1)) As the_side
FROM (SELECT pid, pid_year, the_boundary,
ST_NumPoints(the_boundary) As nump
FROM (SELECT pid, pid_year, ST_Boundary(the_geom) As
the_boundary
FROM assessing.parceltime
WHERE pid_year = 2008) p) As b
CROSS JOIN generate_series(1, 1000) n
WHERE n < b.nump;
UPDATE assessing.parcdimstime_2008 SET sd_length = ST_Length(the_geom);
Thanks,
Regina
-
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mai
RE: [postgis-users] Getting dimensions of polygon sides
oops... below
"create table parcel_dims (pin text, segment numeric(11,3), length float);"
should be
"create table parcel_dims (pin text, segment integer, length numeric(11,3));
...
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of
Randall, Eric
Sent: Wednesday, June 11, 2008 2:57 PM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] Getting dimensions of polygon sides
Hi Regina,
A very non-elegant try on my parcels.
This just ends up making lines out of every two point segment of a parcel poly
and IDs them in order.
Eric
CREATE OR REPLACE FUNCTION taxparcel_to_segments() RETURNS integer AS $$
DECLARE
getrow CURSOR FOR SELECT * FROM taxparcel;
tp_row taxparcel%ROWTYPE;
vsegment integer;
maxrows integer;
maxpoints integer;
i integer;
j integer;
rowcount integer;
BEGIN
/* select DropGeometryColumn('','parcel_dims','geom');
drop table parcel_dims;
create table parcel_dims (pin text, segment numeric(11,3), length
float);
select
addgeometrycolumn('public','parcel_dims','geom',2271,'LINESTRING',2);*/
TRUNCATE parcel_dims;
SELECT INTO maxrows count(*)FROM taxparcel;
OPEN getrow;
rowcount := 0;
i := 1;
WHILE rowcount < maxrows LOOP -- might want to limit this to 100 to
test
FETCH getrow into tp_row;
rowcount := rowcount + 1;
select into maxpoints ST_npoints(tp_row.geom);
FOR i in 1 .. maxpoints - 1 LOOP
vsegment := i;
j := i + 1;
INSERT INTO parcel_dims VALUES
( tp_row.taxpin, vsegment,
ST_length(ST_makeline(ST_pointn(ST_boundary(tp_row.geom),i),ST_pointn(ST_boundary
(tp_row.geom),j)))::numeric(11,3),
ST_makeline(ST_pointn(ST_boundary(tp_row.geom),i),ST_pointn(boundary(tp_row.geom),j))
);
END LOOP;
END LOOP;
RETURN(rowcount);
END;
$$ language plpgsql
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Obe,
Regina
Sent: Wednesday, June 11, 2008 11:02 AM
To: PostGIS Users Discussion
Subject: [postgis-users] Getting dimensions of polygon sides
I'm trying to similuate ArcGIS annotations. I guess ArcPad doesn't
support ArcGIS annotations according to what I have been told but can
support line strings and so forth.
Here is the the problem. I have a set of parcel polygon geometries.
I'm going to assume that each lines side is composed of 2 points and no
polygon has more than 1000 sides. I need to create a table that has a
separate row for each side with the length of that side as an attribute
fields.
I stupidly thought I could take the boundary and then figure out the
length of the boundary forgetting that this just gives me the perimeter.
So my second thought was that if I reconstitute points of the boundary
grouping 2 at a time - that would do the trick.
My query is still running so haven't looked to see what the final result
is. I'm wondering if someone has done something similar and if they
have an easier way.
Below is the query I am testing right now.
INSERT INTO assessing.parcdimstime_2008(pid, pid_year, the_geom)
SELECT b.pid, b.pid_year, ST_MakeLine(ST_PointN(the_boundary,n),
ST_PointN(the_boundary, n + 1)) As the_side
FROM (SELECT pid, pid_year, the_boundary,
ST_NumPoints(the_boundary) As nump
FROM (SELECT pid, pid_year, ST_Boundary(the_geom) As
the_boundary
FROM assessing.parceltime
WHERE pid_year = 2008) p) As b
CROSS JOIN generate_series(1, 1000) n
WHERE n < b.nump;
UPDATE assessing.parcdimstime_2008 SET sd_length = ST_Length(the_geom);
Thanks,
Regina
-
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
___
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] Getting dimensions of polygon sides
Hi Regina,
A very non-elegant try on my parcels.
This just ends up making lines out of every two point segment of a parcel poly
and IDs them in order.
Eric
CREATE OR REPLACE FUNCTION taxparcel_to_segments() RETURNS integer AS $$
DECLARE
getrow CURSOR FOR SELECT * FROM taxparcel;
tp_row taxparcel%ROWTYPE;
vsegment integer;
maxrows integer;
maxpoints integer;
i integer;
j integer;
rowcount integer;
BEGIN
/* select DropGeometryColumn('','parcel_dims','geom');
drop table parcel_dims;
create table parcel_dims (pin text, segment numeric(11,3), length
float);
select
addgeometrycolumn('public','parcel_dims','geom',2271,'LINESTRING',2);*/
TRUNCATE parcel_dims;
SELECT INTO maxrows count(*)FROM taxparcel;
OPEN getrow;
rowcount := 0;
i := 1;
WHILE rowcount < maxrows LOOP -- might want to limit this to 100 to
test
FETCH getrow into tp_row;
rowcount := rowcount + 1;
select into maxpoints ST_npoints(tp_row.geom);
FOR i in 1 .. maxpoints - 1 LOOP
vsegment := i;
j := i + 1;
INSERT INTO parcel_dims VALUES
( tp_row.taxpin, vsegment,
ST_length(ST_makeline(ST_pointn(ST_boundary(tp_row.geom),i),ST_pointn(ST_boundary
(tp_row.geom),j)))::numeric(11,3),
ST_makeline(ST_pointn(ST_boundary(tp_row.geom),i),ST_pointn(boundary(tp_row.geom),j))
);
END LOOP;
END LOOP;
RETURN(rowcount);
END;
$$ language plpgsql
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Obe,
Regina
Sent: Wednesday, June 11, 2008 11:02 AM
To: PostGIS Users Discussion
Subject: [postgis-users] Getting dimensions of polygon sides
I'm trying to similuate ArcGIS annotations. I guess ArcPad doesn't
support ArcGIS annotations according to what I have been told but can
support line strings and so forth.
Here is the the problem. I have a set of parcel polygon geometries.
I'm going to assume that each lines side is composed of 2 points and no
polygon has more than 1000 sides. I need to create a table that has a
separate row for each side with the length of that side as an attribute
fields.
I stupidly thought I could take the boundary and then figure out the
length of the boundary forgetting that this just gives me the perimeter.
So my second thought was that if I reconstitute points of the boundary
grouping 2 at a time - that would do the trick.
My query is still running so haven't looked to see what the final result
is. I'm wondering if someone has done something similar and if they
have an easier way.
Below is the query I am testing right now.
INSERT INTO assessing.parcdimstime_2008(pid, pid_year, the_geom)
SELECT b.pid, b.pid_year, ST_MakeLine(ST_PointN(the_boundary,n),
ST_PointN(the_boundary, n + 1)) As the_side
FROM (SELECT pid, pid_year, the_boundary,
ST_NumPoints(the_boundary) As nump
FROM (SELECT pid, pid_year, ST_Boundary(the_geom) As
the_boundary
FROM assessing.parceltime
WHERE pid_year = 2008) p) As b
CROSS JOIN generate_series(1, 1000) n
WHERE n < b.nump;
UPDATE assessing.parcdimstime_2008 SET sd_length = ST_Length(the_geom);
Thanks,
Regina
-
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
___
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] Buffer select from one layer to another?
Shouldn't you use intersects() rather than touches()? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Abram Gillespie Sent: Wednesday, June 11, 2008 1:55 PM To: PostGIS Users Discussion Subject: [postgis-users] Buffer select from one layer to another? Hi All, I'm finally starting to do some real production work ... after years of simply using PostGIS in example scenarios as I developed zigGIS. Pretty funny, actually. Anywho, could someone enlighten me on how to buffer a road and return the surrounding buildings? This is what I have so far: select h.* from houses h,roads r where r.name like '%MAIN%' and touches(h.the_geom, buffer(r.the_geom,500)); Now I realize the above is a bit complex since " like '%MAIN%' " returns multiple road segments, but even working on a single road: select h.* from houses h,roads r where r.gid=1663 and touches(h.the_geom, buffer(r.the_geom,500)); doesn't seem to return anything. I'm positive the buffer query should return some houses. Thanks for any help! -Abe zigGIS Team Obtuse Software, http://www.obtusesoft.com ___ 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] hexagonal grid
You are the Man, Stan!
Eric
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of
Sufficool, Stanley
Sent: Thursday, May 29, 2008 4:21 PM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] hexagonal grid
Generate a hexagonal grid of 64x128 hexes, then follow the slice/dice from
bostongis at step 4.
Substitute the values in generate series with your datasource min/max X/Y
extents.
===
create table hex_grid (gid serial not null primary key, the_geom geometry not
null);
INSERT INTO hex_grid (the_geom)
SELECT translate(the_geom, x_series, y_series)
from generate_series(0 - 128, 1 + 128, 128) as x_series,
generate_series(0 - 128, 1 + 128, 256) as y_series,
(
SELECT 'POLYGON((0 0,64 64,64 128,0 192,-64 128,-64 64,0 0))'::geometry
as the_geom
UNION SELECT translate('POLYGON((0 0,64 64,64 128,0 192,-64 128,-64
64,0 0))'::geometry, 64, 128) as the_geom
) as one_hex
===
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Matias Massigoge
Sent: Thursday, May 29, 2008 12:58 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] hexagonal grid
Thanks Paul!!
On Thu, May 29, 2008 at 4:42 PM, Paul Ramsey <[EMAIL PROTECTED]> wrote:
> Try this:
>
> http://www.bostongis.com/blog/index.php?/archives/35-Map-Dicing-and-ot
> her-stuff.html
>
> P.
>
> On Thu, May 29, 2008 at 12:34 PM, Matias Massigoge
> <[EMAIL PROTECTED]> wrote:
>> Hi all,
>>
>> I'm looking for something similar but squared.
>> This theme have been covered in the past?
>> Sorry, I'm newby.
>> Thanks all.
>>
>> Matías
>>
>>
>>
>> On Thu, May 29, 2008 at 12:42 PM, Brent Wood <[EMAIL PROTECTED]>
>> wrote:
>>>
>>>> On Thu, May 29, 2008 at 7:08 AM, Randall, Eric
>>>> <[EMAIL PROTECTED]>
>>> wrote:
>>>> > Has anyone created a method for generating a hexagonal grid over
>>>> > a polygon
>>>> envelope using postgis? Thought I'd ask before setting out.
>>>> Thanks.
>>>
>>> Hi Eric
>>>
>>> I haven't specifically done this in PostGIS, but have briefly looked
>>> into something similar with a colleague who uses R extensively, and
>>> was looking at cellular automata modelling over a hexagonal grid. He
>>> was using such a dataset in R, & there were a few ways this could be
>>> transferred to PostGIS, including the R:gdal package, or a
>>> dump/parse/reformat/import script.
>>>
>>> Cheers,
>>>
>>> Brent Wood ___
>>> 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
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
RE: [postgis-users] hexagonal grid
No, but close I think. :~) I remember an interesting grid wrangling thread here a while back and it got me thinking (not a good thing this time of year). Eric -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Paul Ramsey Sent: Thursday, May 29, 2008 11:03 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] hexagonal grid Eric, Is that a marxan in your pocket, or are you just happy to see us? :) P (Sorry, no grid help, just cheesy line...) On Thu, May 29, 2008 at 7:08 AM, Randall, Eric <[EMAIL PROTECTED]> wrote: > Has anyone created a method for generating a hexagonal grid over a polygon > envelope using postgis? Thought I'd ask before setting out. Thanks. > > -Eric > ___ > 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] hexagonal grid
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Brent Wood Sent: Thursday, May 29, 2008 11:43 AM To: PostGIS Users Discussion; [EMAIL PROTECTED] Subject: Re: [postgis-users] hexagonal grid > On Thu, May 29, 2008 at 7:08 AM, Randall, Eric <[EMAIL PROTECTED]> wrote: > > Has anyone created a method for generating a hexagonal grid over a polygon > envelope using postgis? Thought I'd ask before setting out. Thanks. Hi Eric I haven't specifically done this in PostGIS, but have briefly looked into something similar with a colleague who uses R extensively, and was looking at cellular automata modelling over a hexagonal grid. He was using such a dataset in R, & there were a few ways this could be transferred to PostGIS, including the R:gdal package, or a dump/parse/reformat/import script. Cheers, Brent Wood ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users Brent, I've heard of the hexbin() function/package in splus/R that does this. Is that what you were using? I'm hoping to do something in PostGIS itself. Eric ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] hexagonal grid
Has anyone created a method for generating a hexagonal grid over a polygon envelope using postgis? Thought I'd ask before setting out. Thanks. -Eric ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
RE: [postgis-users] ANN: zigGIS 2.0 Released
Do you have a 30 day trial or something? I can't pay $279 per license before knowing it will work for my needs. Thanks. Eric -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Abram Gillespie Sent: Wednesday, May 21, 2008 4:07 PM To: PostGIS Users Discussion Subject: [postgis-users] ANN: zigGIS 2.0 Released Obtuse Software has officially opened for business! zigGIS 2.0 is now available for purchase. zigGIS 2.0 connects ArcGIS Desktop directly to PostGIS allowing you to view, analyze, and, best of all, edit PostGIS data. For more information, please see the official press release: http://www.obtusesoft.com/pr.html If you're already convinced, you can go directly to the online store: https://www.obtusesoft.com/store/order.aspx. The free Personal and Educational licenses won't be available for a couple of weeks. The source code will become available sometime thereafter. It's been a storm of activity here and we need some time to get our feet under us. Thank you for your time and patience over the last three years (has it been that long!?). zigGIS Team Obtuse Software ___ 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] Multipolygons to Polygon
geometryn(the_geom,1) -Eric -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of George Silva Sent: Wednesday, April 16, 2008 3:08 PM To: postgis-users@postgis.refractions.net Subject: [postgis-users] Multipolygons to Polygon Hello everyone, I have a converted dataset that has only polygons, but they were imported from ArcGIS as Multipolygons. I need them to become simple polygons. (I dont have any real multi-polygons in the table - they were just imported that way. How can i convert this dataset to regular tyep polygons *question mark* (no question marks on this keyboard :( ) Thx for the help! ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
RE: [postgis-users] Best graphical viewer for PostGIS data
I use OpenJump the most because of the dbquery plugin. Immediate viewing of any query that returns a geometry, derived or otherwise, is the most important feature for me. I use it as sort of a tester/designer for automating recurring tasks that have a spatial analytical component. All editing is done in other software, outside of postgres/postgis, and reloaded monthly. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Pierre Racine Sent: Tuesday, April 08, 2008 11:22 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] Best graphical viewer for PostGIS data Hi, I would like to hear from you guys about the viewer you use to "see" PostGIS geometries. Which one is the best and why? How do they support views, editing, creation, etc... I can list a couple of them: QGIS uDIG GRASS MapServer Any others? Pierre ___ 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] Loading only data that intersects another feature
select condors.* from condors, boundary where st_within(condors.the_geom,boundary.the_geom) or select condors.* from condors, boundary where st_intersects(condors.the_geom,boundary.the_geom) I would suggest that you download and install OpenJump and the dbquery plugin. That way you can experiment with ad hoc spatial queries using the many postgis functions and visually see the results of each. A great way to see postgis functionality. Mezogis is also good for this but I like OpenJump. -Eric -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Marcus C. England Sent: Monday, April 07, 2008 7:23 PM To: postgis-users@postgis.refractions.net Subject: [postgis-users] Loading only data that intersects another feature Hi all, I have a postgis polygon layer representing a site boundary (we will call the layer "boundary") and another postgis point layer with a larger extent representing California condor observations (we will call the layer "condors"). I am using gvSIG which allows SQL restrictions of layer loads from postgis. What SQL statement would I use (keep in mind the above layer names) to only load the "condors" data that intersects "boundary"? -Marcus ___ 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] Turn a Polygon into Lines
Drew, "select st_multi(st_linemerge(st_boundary(shape))) from bleargh" would be the PostGIS equivalent to your informix statement I believe, however that is not what I am trying to do (see Lee's first paragraph below). I don't use Informix but I would certainly be interested in seeing an Informix solution, or DB2, Oracle, or SQL Server 2008 for that matter. Not being PostGIS related though, you should probably send it to me off the list or post it to an informix spatial list, etc. Thanks. -Eric Drew Sheedy wrote: >> In the informix world you would cast the polygon to a geometry and then into >> the line >> >> ie: >> select shape::st_geometry::st_multilinestring from bleargh >> But have been able to cast the geometry type in postgis :( On Mon, Apr 7, 2008 at 1:47 AM, Randall, Eric wrote: > > Lee Hachadoorian wrote: > >* I'm looking through the PostGIS reference, and I can't seem to find a > *>* way to take a geometry of polygons and turn it into lines. What I'm > *>* looking for is something like the ArcGIS Feature to Line geoprocessor, > *>* which will create a line shapefile where each feature is an arc > *>* representing the boundary between neighboring polygons with a field > *>* indicating the ids of the polygon on either side. > *>* > *>* Functions like ST_MakeLine require point geometries, and I don't see > *>* anything else that seems to be what I'm looking for. Any ideas would > *>* be welcome. > *>* > *>* Thanks, > *>* Lee Hachadoorian > *>* PhD Student in Geography > *>* Program in Earth & Environmental Sciences > *>* CUNY Graduate Center > * > > This is a correction to my March 31 post. I should have have tested a > little more and not been in such a hurry I guess. > > I neglected to account for the cases where a non-shared edge needs to end > at the beginning. Adding a case statement > seems to have handled that. I'd like to find a way to reference the > derived table (p4) multiple times to avoid > repeating the query (p5). The Common Table Expression WITH statement > should do this I think but it doesn't seem to be > > supported in my version of PostgreSQL (8.2). Can anyone give me some > ideas? Thanks. > > -Eric > > > /* > > example_turn_poly_into_lines.sql > >replace "polytable" with your poly table. >replace gid value (309) with your poly's gid. > > */ > > > select p4.poly_id,p4.adjacent_poly_id,p4.pos_order,p4.geom > from > ( > > /* Get edges shared with other polys */ > > select t1.gid as poly_id,t2.gid as adjacent_poly_id, > ST_line_locate_point(ST_linemerge(ST_boundary(t1.geom)),ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom) > as pos_order, > > ST_linemerge(ST_intersection(ST_linemerge(ST_boundary(t1.geom)),ST_linemerge(ST_boundary(t2.geom > as geom > from polytable t1, polytable t2 > where t1.gid = 309 > and ST_touches(t1.geom, t2.geom) > > union > > /* >Create edges not shared with other polys. Uses -1 for pseudo external > universe polygon. >Produces extra line(s) where there is more than one non-shared edge, >necessitating the self-join and ST_covers filter at the end. >Need help, more work on this, ...suggestions? > */ > > select p1.poly_id, p1.adjacent_poly_id, p1.position as > pos_order,ST_line_substring(p3.geom,p1.position,p2.position) as geom > > from > ( > select t1.gid as poly_id,-1::integer as adjacent_poly_id, > > ST_line_locate_point(ST_linemerge(ST_boundary(t1.geom)),ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom) > > as position, > ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom > as geom > from polytable t1, polytable t2 > where t1.gid = 309 > and ST_touches(t1.geom, t2.geom) > and > ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom > not in > ( > select > ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom > as geom > from polytable t1, polytable t2 > where t1.gid = 309 > and ST_touches(t1.geom, t2.geom) > ) > ) > as p1, > ( > select t1.gid as poly_id,-1::integer as adjacent_poly_id, > case > ST_line_locate_point(ST_linemerge(ST_boundary(t1.geom)),ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom) > > > when 0 then 1 > else > ST_line_locate_point(ST_linemerge(boundary(t1.geom)),ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom) > > end > as position,
[postgis-users] Turn a Polygon into Lines
Lee Hachadoorian wrote: > I'm looking through the PostGIS reference, and I can't seem to find a > way to take a geometry of polygons and turn it into lines. What I'm > looking for is something like the ArcGIS Feature to Line geoprocessor, > which will create a line shapefile where each feature is an arc > representing the boundary between neighboring polygons with a field > indicating the ids of the polygon on either side. > > Functions like ST_MakeLine require point geometries, and I don't see > anything else that seems to be what I'm looking for. Any ideas would > be welcome. > > Thanks, > Lee Hachadoorian > PhD Student in Geography > Program in Earth & Environmental Sciences > CUNY Graduate Center This is a correction to my March 31 post. I should have have tested a little more and not been in such a hurry I guess. I neglected to account for the cases where a non-shared edge needs to end at the beginning. Adding a case statement seems to have handled that. I'd like to find a way to reference the derived table (p4) multiple times to avoid repeating the query (p5). The Common Table Expression WITH statement should do this I think but it doesn't seem to be supported in my version of PostgreSQL (8.2). Can anyone give me some ideas? Thanks. -Eric /* example_turn_poly_into_lines.sql replace "polytable" with your poly table. replace gid value (309) with your poly's gid. */ select p4.poly_id,p4.adjacent_poly_id,p4.pos_order,p4.geom from ( /* Get edges shared with other polys */ select t1.gid as poly_id,t2.gid as adjacent_poly_id, ST_line_locate_point(ST_linemerge(ST_boundary(t1.geom)),ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom) as pos_order, ST_linemerge(ST_intersection(ST_linemerge(ST_boundary(t1.geom)),ST_linemerge(ST_boundary(t2.geom as geom from polytable t1, polytable t2 where t1.gid = 309 and ST_touches(t1.geom, t2.geom) union /* Create edges not shared with other polys. Uses -1 for pseudo external universe polygon. Produces extra line(s) where there is more than one non-shared edge, necessitating the self-join and ST_covers filter at the end. Need help, more work on this, ...suggestions? */ select p1.poly_id, p1.adjacent_poly_id, p1.position as pos_order,ST_line_substring(p3.geom,p1.position,p2.position) as geom from ( select t1.gid as poly_id,-1::integer as adjacent_poly_id, ST_line_locate_point(ST_linemerge(ST_boundary(t1.geom)),ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom) as position, ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom as geom from polytable t1, polytable t2 where t1.gid = 309 and ST_touches(t1.geom, t2.geom) and ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom not in ( select ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom as geom from polytable t1, polytable t2 where t1.gid = 309 and ST_touches(t1.geom, t2.geom) ) ) as p1, ( select t1.gid as poly_id,-1::integer as adjacent_poly_id, case ST_line_locate_point(ST_linemerge(ST_boundary(t1.geom)),ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom) when 0 then 1 else ST_line_locate_point(ST_linemerge(boundary(t1.geom)),ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom) end as position, ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom as geom from polytable t1, polytable t2 where t1.gid = 309 and ST_touches(t1.geom, t2.geom) and ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom not in ( select ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom as geom from polytable t1, polytable t2 where t1.gid = 309 and ST_touches(t1.geom, t2.geom) ) ) as p2, ( select ST_linemerge(ST_boundary(geom)) as geom from polytable where gid = 309 ) as p3 where p1.position < p2.position ) as p4, /* repeat for needed self join */ ( /* Get edges shared with other polys */ select t1.gid as poly_id,t2.gid as adjacent_poly_id, ST_line_locate_point(ST_linemerge(ST_boundary(t1.geom)),ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom) as pos_order, ST_linemerge(ST_intersection(ST_linemerge(ST_boundary(t1.geom)),ST_linemerge(ST_boundary(t2.geom as geom from polytable t1, polytable t2 where t1.gid = 309 and ST_touches(t1.geom, t2.geom) union /* Create edges not shared with other polys. Uses -1 for pseudo external universe polygon. Produces extra line(s) where there is more than one non-shared edge, necessitating the self-join and ST_covers filter at the end. Need help, more work on this, ...suggestions? */ select p1.p
[postgis-users] Turn a Polygon into Lines
Lee Hachadoorian wrote: > I'm looking through the PostGIS reference, and I can't seem to find a > way to take a geometry of polygons and turn it into lines. What I'm > looking for is something like the ArcGIS Feature to Line geoprocessor, > which will create a line shapefile where each feature is an arc > representing the boundary between neighboring polygons with a field > indicating the ids of the polygon on either side. > > Functions like ST_MakeLine require point geometries, and I don't see > anything else that seems to be what I'm looking for. Any ideas would > be welcome. > > Thanks, > Lee Hachadoorian > PhD Student in Geography > Program in Earth & Environmental Sciences > CUNY Graduate Center A custom function is the fastest and most efficient way to do this, as others have indicated, but it's often fun and instructive to try to implement a task in a single statement. Here's my attempt at the "ArcGIS Feature to Line geoprocessor" mentioned. It needs to be faster and smarter, but seems to work. If anyone has suggestions for this or even a different approach, please chime in. I'm not always good at seeing other paths once I get a ways into the woods. Thanks. -Eric /* turn_poly_into_lines.sql I used parcel polygons in my experiment. Replace "polytable" with your poly table. Replace gid value with your poly's gid. */ select p4.poly_id,p4.adjacent_poly_id,p4.pos_order,p4.geom from ( /* Get edges shared with other polys */ select t1.gid as poly_id,t2.gid as adjacent_poly_id, ST_line_locate_point(ST_linemerge(ST_boundary(t1.geom)),ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom) as pos_order, ST_linemerge(ST_intersection(ST_linemerge(ST_boundary(t1.geom)),ST_linemerge(ST_boundary(t2.geom as geom from polytable t1, polytable t2 where t1.gid = 309 and ST_touches(t1.geom, t2.geom) union /* Create edges not shared with other polys. Uses -1 for pseudo external universe polygon. Produces extra line necessitating the self-join and the ST_covers filter at the end. Need help, more work on this. */ select p1.poly_id, p1.adjacent_poly_id, p1.position as pos_order,ST_line_substring(p3.geom,p1.position,p2.position) as geom from ( select t1.gid as poly_id,-1::integer as adjacent_poly_id, ST_line_locate_point(ST_linemerge(ST_boundary(t1.geom)),ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom) as position, ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom as geom from polytable t1, polytable t2 where t1.gid = 309 and ST_touches(t1.geom, t2.geom) and ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom not in ( select ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom as geom from polytable t1, polytable t2 where t1.gid = 309 and ST_touches(t1.geom, t2.geom) ) ) as p1, ( select t1.gid as poly_id,-1::integer as adjacent_poly_id, ST_line_locate_point(ST_linemerge(ST_boundary(t1.geom)),ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom) as position, ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom as geom from polytable t1, polytable t2 where t1.gid = 309 and ST_touches(t1.geom, t2.geom) and ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom not in ( select ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom as geom from polytable t1, polytable t2 where t1.gid = 309 and ST_touches(t1.geom, t2.geom) ) ) as p2, ( select ST_linemerge(ST_boundary(geom)) as geom from polytable where gid = 309 ) as p3 where p1.position < p2.position ) as p4, /* Repeat for needed self-join */ ( /* Get edges shared with other polys */ select t1.gid as poly_id,t2.gid as adjacent_poly_id, ST_line_locate_point(ST_linemerge(ST_boundary(t1.geom)),ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom) as pos_order, ST_linemerge(ST_intersection(ST_linemerge(ST_boundary(t1.geom)),ST_linemerge(ST_boundary(t2.geom as geom from polytable t1, polytable t2 where t1.gid = 309 and ST_touches(t1.geom, t2.geom) union /* Create edges not shared with other polys. Uses -1 for pseudo external universe polygon. Produces extra line necessitating the self-join and the ST_covers filter at the end. Need help, more work on this. */ select p1.poly_id, p1.adjacent_poly_id, p1.position as pos_order, ST_line_substring(p3.geom,p1.position,p2.position) as geom from ( select t1.gid as poly_id,-1::integer as adjacent_poly_id, ST_line_locate_point(ST_linemerge(ST_boundary(t1.geom)),ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom) as position, ST_end
Re: [postgis-users] How to get the centerline of a polygon?
Kevin and Martin, Thanks for your suggestion! I will try the plug-in. It looks amazing. Eric On Jan 19, 2008 12:35 AM, Kevin Neufeld <[EMAIL PROTECTED]> wrote: > Eric, > > Are you perhaps after generating voronoi skeletons with your areas? > > If so, you may be interested in the free Skeletonizer Utility (SK) > plugin for JUMP that we developed here at Refractions a while back. > http://www.jump-project.org/project.php?PID=SK&SID=OVER > > This utility will generate voronoi skeletons (center lines) given a > polygon and a set of input/output points. > > The attached picture shows the results using a long narrow lake and > stream input/output points as input. > > Cheers, > Kevin > > - > Kevin Neufeld > Software Developer > Refractions Research Inc. > 300-1207 Douglas St. > Victoria, B.C., V8W 2E7 > > Phone: (250) 383-3022 > Email: [EMAIL PROTECTED] > > > > > Eric Yu wrote: > > Hi, > > > > Suppose I have a long shaped polygon, like river area or the road, > > is it possible to get the centerline of the area using PostGIS? > > Or with other GIS library/software? I searched and tried but failed to > > get any solution. > > > > Thank! > > > > Eric ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How to get the centerline of a polygon?
Thanks for your comments. The questions you asked are exactly what I faced while I was searching for answers. It is easy to draw a centerline by human, but I don't even know where to start. My initial idea was like this: (Imagine we have a polygon, shaped like a rope, lying on the plane) 1. First we need to find the two ends of the rope 2. From the two "central points" of the two ends (if we can determine), we can split the exterior ring of the rope into two line strings. 3. Along the two line strings, we can locate the points at 0.01, 0.02, 0.03, ... with this method, maybe we can ignore the unmatched points on the two edges. However, the first step is hard (just as what you said about where to "start" and "end"). I can't figure out a good way to do this. In fact, the problem comes from here: I have a highway road network (up/down line, and ramp branching off, etc.) and I need to draw a simplified line following the road. So I first use ST_Buffer() and then union the buffers into a rope-shaped polygon. And there comes the centerline problem. Maybe there is some other way to achieve this? Thanks! Eric On Jan 18, 2008 11:11 PM, Stephen Woodbridge <[EMAIL PROTECTED]> wrote: > > Eric Yu wrote: > > Hi, > > > > Suppose I have a long shaped polygon, like river area or the road, > > is it possible to get the centerline of the area using PostGIS? > > Or with other GIS library/software? I searched and tried but failed to > > get any solution. > > I think the problem to start with is how are you going to define what > the center line of the polygon in algorithmic terms? Does the center > line change if you rotate it 90 degrees? Assuming you had a center line > of a polygon, how would the points of the centerline relate to the polygon. > > I assume you want some set of points that approximates a path between > the two adjacent polygon virtual "edges", but polygons are made of > multiple points and hence multiple edges so what points belong to which > "edge". Also if the number of points does not match on the opposite > "edge" how should that be handled? How does one determine what points > belong to the "start" and "end" "edge of the polygon that the center > line needs to "start" on and "end" on? > > If you can answer these questions clearly then it is possible to write > an algorithm to do what you want. But just looking at the polygon and > intuiting that there should be a centerline that follows some visual > path the your mind perceives is kind of hard to deal with. > > -Steve W ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] How to get the centerline of a polygon?
Hi, Suppose I have a long shaped polygon, like river area or the road, is it possible to get the centerline of the area using PostGIS? Or with other GIS library/software? I searched and tried but failed to get any solution. Thank! Eric ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Install problem
Hello, I'm new to Postgis, trying to install on Red Hat. I'm getting a variation of an error that shows up a lot in message boards. I'm getting an error running lwpostgis.sql. I configured postgresql with LDFLAGS=-lstdc++. I ran ldconfig after updating /etc/ld.so.conf. The error is: [mydir][~]$ psql -d gistest -f share/lwpostgis.sql BEGIN psql:share/lwpostgis.sql:53: NOTICE: type "histogram2d" is not yet defined DETAIL: Creating a shell type definition. psql:share/lwpostgis.sql:53: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. psql:share/lwpostgis.sql:53: connection to server was lost [mydir][~]$ When I try to run statements alone in phppgadmin, I see this for a create function using a library: *SQL error:* server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. *In statement:* CREATE OR REPLACE FUNCTION ST_histogram2d_in(cstring) RETURNS histogram2d AS '/home/mydir/lib/liblwgeom', 'lwhistogram2d_in' LANGUAGE 'C' IMMUTABLE STRICT; -- WITH (isstrict); The Log on the server has: [mydir][~]$ NOTICE: type "histogram2d" is not yet defined DETAIL: Creating a shell type definition. LOG: server process (PID 4889) was terminated by signal 11 LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing L Here is the postgresql configuration [mydir][~]$ pg_config BINDIR = /usr/home/mydir/bin DOCDIR = /usr/home/mydir/doc/postgresql INCLUDEDIR = /usr/home/mydir/include PKGINCLUDEDIR = /usr/home/mydir/include/postgresql INCLUDEDIR-SERVER = /usr/home/mydir/include/postgresql/server LIBDIR = /usr/home/mydir/lib PKGLIBDIR = /usr/home/mydir/lib/postgresql LOCALEDIR = MANDIR = /usr/home/mydir/man SHAREDIR = /usr/home/mydir/share/postgresql SYSCONFDIR = /usr/home/mydir/etc/postgresql PGXS = /usr/home/mydir/lib/postgresql/pgxs/src/makefiles/pgxs.mk CONFIGURE = '--prefix=/home/mydir' 'LDFLAGS=-lstdc++' CC = gcc CPPFLAGS = -D_GNU_SOURCE CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing CFLAGS_SL = -fpic LDFLAGS = -lstdc++ -Wl,-rpath,'/home/mydir/lib' LDFLAGS_SL = LIBS = -lpgport -lz -lreadline -lncurses -lcrypt -ldl -lm VERSION = PostgreSQL 8.2.6 [mydir][~]$ Any help would be greatly appreciated ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Problem with st_endpoint in postgis 1.3.1 for windows
Hello, I have a problem when I run st_endpoint function : the query kill the process. The same query with st_startpoint works fine. I'm also able to run st_endpoint with postgis 1.2.1 with no problem. My conf Windows XP "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)" "POSTGIS="1.3.1" GEOS="3.0.0rc4-CAPI-1.3.3" PROJ="Rel. 4.5.0, 22 Oct 2006" USE_STATS" The log 2007-08-21 23:11:04 STATEMENT: select y(st _endpoint(the_geom)) from streets limit 10 2007-08-21 23:11:16 LOG: server process (PID 4988) exited with exit code -1073741819 2007-08-21 23:11:16 LOG: terminating any other active server processes 2007-08-21 23:11:16 WARNING: terminating connection because of crash of another server process 2007-08-21 23:11:16 DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2007-08-21 23:11:16 HINT: In a moment you should be able to reconnect to the database and repeat your command. 2007-08-21 23:11:16 WARNING: terminating connection because of crash of another server process 2007-08-21 23:11:16 DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2007-08-21 23:11:16 HINT: In a moment you should be able to reconnect to the database and repeat your command. 2007-08-21 23:11:16 LOG: all server processes terminated; reinitializing 2007-08-21 23:11:16 LOG: database system was interrupted at 2007-08-21 23:09:07 Paris, Madrid 2007-08-21 23:11:16 LOG: checkpoint record is at 5/D3ECC1D0 2007-08-21 23:11:16 LOG: redo record is at 5/D3ECC1D0; undo record is at 0/0; shutdown FALSE 2007-08-21 23:11:16 LOG: next transaction ID: 0/1226346; next OID: 101454 2007-08-21 23:11:16 LOG: next MultiXactId: 1; next MultiXactOffset: 0 2007-08-21 23:11:16 LOG: database system was not properly shut down; automatic recovery in progress 2007-08-21 23:11:16 LOG: redo starts at 5/D3ECC220 2007-08-21 23:11:16 LOG: record with zero length at 5/D42FAF58 2007-08-21 23:11:16 LOG: redo done at 5/D42FAF28 2007-08-21 23:11:18 LOG: database system is ready Thanks for your help _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
