Re: [postgis-users] set srid in geometry column

[Richard Greenwood]

On Sat, Jan 1, 2011 at 11:49 AM, didier peeters  wrote:
> Hi everybody,
>
> I use QGis to display postgis data and in the geometry column I have srid = 
> '-1' (no value).  Everything works fine, I only have to specify the srid in 
> the QGis project but when I set the right srid directly into the geometry 
> column table (srid = 4326, for WGS 84) I get the following error when trying 
> to load the layer in QGis.

The -1 in the geometry_columns table probably means that a SRID was
not specified when the data was imported. The updategeometrysrid()
function can be used to assign a SRID to the geometry objects in a
table. This does not transform the data to a new coordinate system, it
simply assigns a new SRID, so it's up to you to choose the correct
SRID:

   select updategeometrysrid('schema_name_optional', 'table_name','
geometry_column_name', 4326);

After assigning the new (hopefully correct) SRID to all of geometry
objects in the table, use the populate_geometry_columns() to update
the geometry_columns table. This function examines all tables and view
in the database and inserts/deletes/updates rows as appropriate.

   select populate_geometry_columns();

Rich

-- 
Richard Greenwood
richard.greenw...@gmail.com
www.greenwoodmap.com
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Trying to import natural earth data into

[David Blackman]

what's v.clean? It seemed for me that just v.ogr.in + v.ogr.out fixed
all my natural earth errors

out of curiosity, why not include admin-1-provinces too? Having
state-level data seems  to be one of the biggest differentiators of
the data set over most other public data sets.

--dave

On Sat, Jan 1, 2011 at 7:39 PM, David Blackman  wrote:
> what's v.clean? It seemed for me that just v.ogr.in + v.ogr.out fixed
> all my natural earth errors
>
> out of curiosity, why not include admin-1-provinces too? Having
> state-level data seems  to be one of the biggest differentiators of
> the data set over most other public data sets.
>
> --dave
>
> On Sat, Jan 1, 2011 at 3:52 PM, Brian Hamlin  wrote:
>> Happy 2011 all:
>>
>>  We load the natural earth data set into PostGIS on the OSGeo LiveDVD
>> and in fact, are encouraging all LiveDVD projects to use it as the demo set.
>>
>> The scripts only load seven primary tables, not the complete 40+
>> I have confirmed there are st_isvalid() errors in those tables
>> The tables are
>>
>>  10m-admin-0-countries
>>  10m-urban-area
>>  10m_lakes
>>  10m_land
>>  10m_ocean
>>  10m_populated_places_simple
>>  10m_rivers_lake_centerlines
>>
>>  I just looked into doing the round-trip in GRASS with v.clean
>> Its rather tedious/manual.. and, not sure where that data result would go.
>>
>> I estimate about a long day to do a complete GRASS v.clean treatment
>> for at least the primary seven tables we use.. perhaps all 40+, though if
>> there are a lot of errors, that implies more QC involved.
>>
>>  best regards from California
>>   -Brian
>>
>> ==
>> Brian Hamlin
>> planetwork.net
>> OSGeo California Chapter
>> (415) 717-4462 cell
>>
>> ___
>> 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] Trying to import natural earth data into

[Brian Hamlin]

Happy 2011 all:

  We load the natural earth data set into PostGIS on the OSGeo LiveDVD
and in fact, are encouraging all LiveDVD projects to use it as the demo 
set.


The scripts only load seven primary tables, not the complete 40+
I have confirmed there are st_isvalid() errors in those tables
The tables are

  10m-admin-0-countries
  10m-urban-area
  10m_lakes
  10m_land
  10m_ocean
  10m_populated_places_simple
  10m_rivers_lake_centerlines

  I just looked into doing the round-trip in GRASS with v.clean
Its rather tedious/manual.. and, not sure where that data result would 
go.


I estimate about a long day to do a complete GRASS v.clean treatment
for at least the primary seven tables we use.. perhaps all 40+, though 
if

there are a lot of errors, that implies more QC involved.

  best regards from California
   -Brian

==
Brian Hamlin
planetwork.net
OSGeo California Chapter
(415) 717-4462 cell

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] set srid in geometry column

[didier peeters]

Hi everybody,

I use QGis to display postgis data and in the geometry column I have srid = 
'-1' (no value).  Everything works fine, I only have to specify the srid in the 
QGis project but when I set the right srid directly into the geometry column 
table (srid = 4326, for WGS 84) I get the following error when trying to load 
the layer in QGis.  

1 cursor states lost.
SQL: CLOSE qgisf0
Résultat: 7 (ERROR: current transaction is aborted, commands ignored until end 
of transaction block)


Can anybody help me ?

Thanks !

Didier
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] shp2pgsql with DBF >= 2 GB?

[Aren Cambre]

Does it cause a regression for 32-bit users?

Aren

On Sat, Jan 1, 2011 at 11:16 AM, Paul Ramsey wrote:

> On 64bit machines it works.
>
> P.
>
> On 2011-01-01, at 5:33 AM, Aren Cambre  wrote:
>
> It was raised there 4.5 years ago: 
> 
> http://bugzilla.maptools.org/show_bug.cgi?id=1463.
>
> Might be nice if PostGIS maintainers could "bug" that developer about
> resolution?
>
> The proposed local patch is changing int to long on three variables. While
> that may not be graceful, could it work as a temporary workaround?
>
> Aren
>
> On Sat, Jan 1, 2011 at 7:05 AM, Mark Cave-Ayland 
> <
> mark.cave-ayl...@siriusit.co.uk> wrote:
>
>> On 01/01/11 06:46, Paul Ramsey wrote:
>>
>>  No workarounds. I thought there was a braindead patch on that ticket
>>> already, but it's not a patch I feel good enough to apply as a
>>> permanent fix.
>>>
>>> P.
>>>
>>
>> Yeah. Also since we've recently resynced with upstream shapelib, I'd much
>> rather the issue is raised/fixed there and then we can just resync once
>> again.
>>
>>
>> ATB,
>>
>> Mark.
>>
>> --
>> Mark Cave-Ayland - Senior Technical Architect
>> PostgreSQL - PostGIS
>> Sirius Corporation plc - control through freedom
>>  http://www.siriusit.co.uk
>> t: +44 870 608 0063
>>
>> Sirius Labs: 
>> http://www.siriusit.co.uk/labs
>>
>> ___
>> postgis-users mailing list
>>  
>> postgis-users@postgis.refractions.net
>>  
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>
> ___
> postgis-users 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] shp2pgsql with DBF >= 2 GB?

[Paul Ramsey]

On 64bit machines it works. 

P.

On 2011-01-01, at 5:33 AM, Aren Cambre  wrote:

> It was raised there 4.5 years ago: 
> http://bugzilla.maptools.org/show_bug.cgi?id=1463.
> 
> Might be nice if PostGIS maintainers could "bug" that developer about 
> resolution?
> 
> The proposed local patch is changing int to long on three variables. While 
> that may not be graceful, could it work as a temporary workaround?
> 
> Aren
> 
> On Sat, Jan 1, 2011 at 7:05 AM, Mark Cave-Ayland 
>  wrote:
> On 01/01/11 06:46, Paul Ramsey wrote:
> 
> No workarounds. I thought there was a braindead patch on that ticket
> already, but it's not a patch I feel good enough to apply as a
> permanent fix.
> 
> P.
> 
> Yeah. Also since we've recently resynced with upstream shapelib, I'd much 
> rather the issue is raised/fixed there and then we can just resync once again.
> 
> 
> ATB,
> 
> Mark.
> 
> -- 
> Mark Cave-Ayland - Senior Technical Architect
> PostgreSQL - PostGIS
> Sirius Corporation plc - control through freedom
> http://www.siriusit.co.uk
> t: +44 870 608 0063
> 
> Sirius Labs: http://www.siriusit.co.uk/labs
> 
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> ___
> postgis-users 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] shp2pgsql with DBF >= 2 GB?

[Mark Cave-Ayland]

On 01/01/11 13:33, Aren Cambre wrote:


It was raised there 4.5 years ago:
http://bugzilla.maptools.org/show_bug.cgi?id=1463.

Might be nice if PostGIS maintainers could "bug" that developer about
resolution?

The proposed local patch is changing int to long on three variables.
While that may not be graceful, could it work as a temporary workaround?

Aren


Right. Have you actually tried the patch to see if it works? If it does, 
I'd attach it to the shapelib bug report and then ask on the relevant 
mailing list to get one of the developers to test and commit.



ATB,

Mark.

--
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] query error when combining UPDATE, CASE and WHERE

[Mark Cave-Ayland]

On 31/12/10 17:23, zhang zhe wrote:


Hello,

I try to update one column value based the other two column values´s
conditions. This code works

select book, case when table1.price>200 and table1.price<400 then
'40'else book end from table 1 where table1.catalog='112';

Now I want to update the table1, so the book value will change into 40
when the other two conditions are true.

update book, case when table1.price>200 and table1.price<400 then '40'
else book end from table 1 where table1.catalog='112';

It gives error. Can anyone help me?


You probably want something along the lines of this (untested so please 
check it first):


update table1 set book = (select case when price > 200 and price < 400 
then '40' else book end) where catalog = '112';



HTH,

Mark.

--
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] shp2pgsql with DBF >= 2 GB?

[Aren Cambre]

It was raised there 4.5 years ago:
http://bugzilla.maptools.org/show_bug.cgi?id=1463.

Might be nice if PostGIS maintainers could "bug" that developer about
resolution?

The proposed local patch is changing int to long on three variables. While
that may not be graceful, could it work as a temporary workaround?

Aren

On Sat, Jan 1, 2011 at 7:05 AM, Mark Cave-Ayland <
mark.cave-ayl...@siriusit.co.uk> wrote:

> On 01/01/11 06:46, Paul Ramsey wrote:
>
>  No workarounds. I thought there was a braindead patch on that ticket
>> already, but it's not a patch I feel good enough to apply as a
>> permanent fix.
>>
>> P.
>>
>
> Yeah. Also since we've recently resynced with upstream shapelib, I'd much
> rather the issue is raised/fixed there and then we can just resync once
> again.
>
>
> ATB,
>
> Mark.
>
> --
> Mark Cave-Ayland - Senior Technical Architect
> PostgreSQL - PostGIS
> Sirius Corporation plc - control through freedom
> http://www.siriusit.co.uk
> t: +44 870 608 0063
>
> Sirius Labs: http://www.siriusit.co.uk/labs
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Null value when inserting large linestrings or multilinestrings in windows

[Mark Cave-Ayland]

On 31/12/10 00:02, Stanley Sufficool wrote:


This question has come up sooo many times in so many lists. Why not
just have PGAdmin return a  or some
indicator besides blank to indicate a too large value??


That sounds like a good plan - feel free to raise this on the 
pgadmin-support list (see http://www.pgadmin.org/development/list.php 
for more information).



ATB,

Mark.

--
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] shp2pgsql with DBF >= 2 GB?

[Mark Cave-Ayland]

On 01/01/11 06:46, Paul Ramsey wrote:


No workarounds. I thought there was a braindead patch on that ticket
already, but it's not a patch I feel good enough to apply as a
permanent fix.

P.


Yeah. Also since we've recently resynced with upstream shapelib, I'd 
much rather the issue is raised/fixed there and then we can just resync 
once again.



ATB,

Mark.

--
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users