Mathieu Basille | 1 Oct 03:10 2011

Re: Convert from character to timestamptz

Le 30/09/2011 17:49, Sandro Santilli a écrit :
>> You're right, I'm being too vague (in my defence, error messages are
>> in French for me!).
>
> See if exporting LANG=C helps there.

Did you mean at the command line or in the SQL query window? I tried 
both with no success.
I also tried to launch pgAdmin with 'LC_ALL=C pgadmin3', but error 
messages were still in French. What's weird is that the whole software 
runs in English!

> I was wrong about the syntax, but the idea is that you tell it
> how to do the conversion. Maybe it was USING instead of WITH.
> The definitive answer is in the PostgreSQL reference manual, under
> the SQL section, in the ALTER TABLE syntax.

That's beautiful! I've seen this page previously, but couldn't figure 
out what was the correct command for my case. Now, with:

ALTER TABLE test
     ALTER COLUMN date TYPE timestamptz
     USING date::timestamptz;

I have now date as a 'timestamptz'! I checked, and the timezone is taken 
into account (winter times are GMT-5, summer times are GMT-4). I think 
I'm starting to understand the mistakes I made and the rationale behind 
this command.

Sandro, thanks a lot!
(Continue reading)

Puneet Kishor | 1 Oct 03:20 2011
Picon

ArcGIS GRID data

What are my options for storing and displaying GRID data? That is, assuming I am using the current stable
version of Pg (1.5.x) and don't want to play with the bleading edge.

--
Puneet Kishor 
pcreso | 1 Oct 06:15 2011

Re: ArcGIS GRID data

I have converted such grids to XYZ points using GDAL & stored them as Postgis points, but ensure you are clear about your pixel vs grid registration (are the X&Y coords at cell origin or centre?)

I'll leave it to you to determine whether upgrading to a version supporting PGRASTER is viable or preferable.



--- On Sat, 10/1/11, Puneet Kishor <punk.kish <at> gmail.com> wrote:

From: Puneet Kishor <punk.kish <at> gmail.com>
Subject: [postgis-users] ArcGIS GRID data
To: "PostGIS Users Discussion" <postgis-users <at> postgis.refractions.net>
Date : Saturday, October 1, 2011, 2:20 PM

What are my options for storing and displaying GRID data? That is, assuming I am using the current stable version of Pg (1.5.x) and don't want to play with the bleading edge.


--
Puneet Kishor

_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Sandro Santilli | 1 Oct 09:02 2011
Picon

Re: Convert from character to timestamptz

On Fri, Sep 30, 2011 at 09:10:27PM -0400, Mathieu Basille wrote:
> Le 30/09/2011 17:49, Sandro Santilli a écrit :
> >>You're right, I'm being too vague (in my defence, error messages are
> >>in French for me!).
> >
> >See if exporting LANG=C helps there.
> 
> Did you mean at the command line or in the SQL query window? I tried
> both with no success.
> I also tried to launch pgAdmin with 'LC_ALL=C pgadmin3', but error
> messages were still in French. What's weird is that the whole
> software runs in English!

You probably need that variable to be in the backend environment, as 
that's where the messages originate from. But a user-driven language
support would make much more sense so maybe it _is_ possible to do, 
seek the web about it, there might be a way to signal the desired
language from the frontend to the backend (some set variable thing)
and if not you could file a feature request for pgsql hackers :)

> ALTER TABLE test
>     ALTER COLUMN date TYPE timestamptz
>     USING date::timestamptz;
> 
> I have now date as a 'timestamptz'! 

Great ! 

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html
Pierre Racine | 1 Oct 15:15 2011
Picon

Re: WKT Raster

We should probably at some point provide a ST_SetValues(raster, ARRAY[values]). Would this help?

Pierre

> -----Original Message-----
> From: postgis-users-bounces <at> postgis.refractions.net [mailto:postgis-users-
> bounces <at> postgis.refractions.net] On Behalf Of Volkmar Herbst privat
> Sent: Tuesday, September 27, 2011 3:54 PM
> To: postgis-users <at> postgis.refractions.net
> Subject: [postgis-users] WKT Raster
> 
> Hi all,
> I have a question concerning the creation of a raster in a plr function. I create a
> empty raster and set the pixel values individually with update
> queries:
> 
> for(i in 1:nrow(mat_p)) {
>       for(j in 1:ncol(mat_p)) {
> pg.spi.exec(sprintf ("UPDATE nutrients.applications SET raster =
> ST_SetValue(raster,%s, %s, %s,%s) WHERE id = %s ;", 3, j, i, mat_cao[i,j],
> rid))
>       }
> }
> 
> This takes quite a long time..
> Does anyone know an alternative approach to create a WKT- Raster in R directly
> without setting each pixel value and export it to a Postgis table?
> Or is it possible to write the binary raster somehow?
> Any hints are most welcome,
> 
> Volkmar
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users <at> postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
Puneet Kishor | 1 Oct 17:07 2011
Picon

Re: ArcGIS GRID data


On Sep 30, 2011, at 11:15 PM, pcreso <at> pcreso.com wrote:

> I have converted such grids to XYZ points using GDAL & stored them as Postgis points, but ensure you are
clear about your pixel vs grid registration (are the X&Y coords at cell origin or centre?)

Thanks Brent. This is good advice. I am still at a prelim stage determining the best foot forward. My
choices, as I knew until now, were tiff and grid. I am intrigued by the idea of storing them as Pg points, but
am concerned about data inflation. A couple of thousand (or more) points in each direction can result in
several million points, and the db overhead per row can be killing.

> 
> I'll leave it to you to determine whether upgrading to a version supporting PGRASTER is viable or preferable.
> 

At some point, when the stable version arrives, then I would definitely consider it. Right now I have little
latitude with testing non-stable software.

Many thanks again. Good to hear another pov.

> 
> 
> --- On Sat, 10/1/11, Puneet Kishor <punk.kish <at> gmail.com> wrote:
> 
> From: Puneet Kishor <punk.kish <at> gmail.com>
> Subject: [postgis-users] ArcGIS GRID data
> To: "PostGIS Users Discussion" <postgis-users <at> postgis.refractions.net>
> Date: Saturday, October 1, 2011, 2:20 PM
> 
> What are my options for storing and displaying GRID data? That is, assuming I am using the current stable
version of Pg (1.5.x) and don't want to play with the bleading edge.
> 
> 
> --
> Puneet Kishor 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users <at> postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
atekgul | 2 Oct 13:24 2011
Picon

ERROR: invalid input syntax for integer: "POINT(389 418.12 4420549.100044)"

Hi,

I am trying to insert geometry information into a point table. But when I try to execute following query, I am getting " ERROR:  invalid input syntax for integer: "POINT(389418.12 4420549.100044)" " message. I cannot get a solution from my searches.
Is there anyone who has solution to this problem?

QUERY :

INSERT INTO pol_p (x, y,the_geom) VALUES (0, 0, GeomFromText('POINT(389418.12 4420549.100044)' -1));

ERROR :

ERROR:  invalid input syntax for integer: "POINT(389418.12 4420549.100044)"

Here is my table SQL statement :

CREATE TABLE pol_p
(
  gid serial NOT NULL,
  the_geom geometry(Point,2097151),
  x double precision DEFAULT 0,
  y double precision DEFAULT 0,
  CONSTRAINT pol_p_pkey PRIMARY KEY (gid )
)
WITH (
  OIDS=TRUE
);

 
Best regards...

Ayhan Tekgul
_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Gery . | 2 Oct 13:41 2011
Picon

csv table to points and then create line based on same name


Hello,

I'm new around here and after working a while with postgis/postgresql, I'd like to get something that up to know is advanced for me. Please don't get mad at me if this is a stupid question =)

I want to create a line table based on a point table, which it is composed of rows with equal names, this is an example of this csv table:

===
PROFILE,LONGITUDE,LATITUDE
HH00-23,-80.2835,-8.05167
HH00-23,-80.633,-8.20033
HH00-22,-80.6027,-8.2655
HH00-22,-80.2018,-8.094
...
...
===

I loaded this csv table into my database without problems, so this is how it looks like:

===
profile  | longitude | latitude |                        geom                       
----------+-----------+----------+----------------------------------------------------
 HH00-23  |  -80.2835 | -8.05167 | 0101000020E6100000A01A2FDD241254C05F5E807D741A20C0
 HH00-23  |   -80.633 | -8.20033 | 0101000020E61000008D976E12832854C0BBD05CA7916620C0
 HH00-22  |  -80.6027 |  -8.2655 | 0101000020E6100000265305A3922654C00E2DB29DEF8720C0
 HH00-22  |  -80.2018 |   -8.094 | 0101000020E61000004D158C4AEA0C54C0E3A59BC4203020C0
...
...
===

now comes the part I don't know how to solve it, I want to get a table like this:

===
profile | comments |                geom
-------------------------------------------------------------
HH00-23 | some stuff | "HERE THE GEOMETRY SHOULD BE A LINE!"
HH00-22 | some stuff | "HERE THE GEOMETRY SHOULD BE A LINE!"
...
...
===

the case is that I have these points (from the csv table) in order, so, a straight line should be build after connecting the points. Here I showed just two pairs of points in each case, but I have also in that table 6 to 10 points with the same profile name. I did this in arcgis manually and it is painful, I think that postgis is definitively more practice.

Any hint is very welcome,

Gery
_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Andy Colson | 2 Oct 14:51 2011
Picon

Re: csv table to points and then create line based on same name

On 10/02/2011 06:41 AM, Gery . wrote:
>
> Hello,
>
> I'm new around here and after working a while with postgis/postgresql, I'd like to get something that up to
know is advanced for me. Please don't get mad at me if this is a stupid question =)
>
> I want to create a line table based on a point table, which it is composed of rows with equal names, this is an
example of this csv table:
>
> ===
> PROFILE,LONGITUDE,LATITUDE
> HH00-23,-80.2835,-8.05167
> HH00-23,-80.633,-8.20033
> HH00-22,-80.6027,-8.2655
> HH00-22,-80.2018,-8.094
> ...
> ...
> ===
>
> I loaded this csv table into my database without problems, so this is how it looks like:
>
> ===
> profile | longitude | latitude | geom
> ----------+-----------+----------+----------------------------------------------------
> HH00-23 | -80.2835 | -8.05167 | 0101000020E6100000A01A2FDD241254C05F5E807D741A20C0
> HH00-23 | -80.633 | -8.20033 | 0101000020E61000008D976E12832854C0BBD05CA7916620C0
> HH00-22 | -80.6027 | -8.2655 | 0101000020E6100000265305A3922654C00E2DB29DEF8720C0
> HH00-22 | -80.2018 | -8.094 | 0101000020E61000004D158C4AEA0C54C0E3A59BC4203020C0
> ...
> ...
> ===
>
> now comes the part I don't know how to solve it, I want to get a table like this:
>
> ===
> profile | comments | geom
> -------------------------------------------------------------
> HH00-23 | some stuff | "HERE THE GEOMETRY SHOULD BE A LINE!"
> HH00-22 | some stuff | "HERE THE GEOMETRY SHOULD BE A LINE!"
> ...
> ...
> ===
>
> the case is that I have these points (from the csv table) in order, so, a straight line should be build after
connecting the points. Here I showed just two pairs of points in each case, but I have also in that table 6 to
10 points with the same profile name. I did this in arcgis manually and it is painful, I think that postgis is
definitively more practice.
>
> Any hint is very welcome,
>
> Gery

One little problem I can see is the order of the points will be undefined.  For example

HH00-23,-80,-8
HH00-23,-81,-8
HH00-23,-80,-7
HH00-23,-81.-7

after you dump theses into a table, then turn around and select them, if you dont put an order by on a select
statement, then PG can return them in any order it wants.  And "order by profile" wont really help, again,
those 4 points can be returned in any order.  Do you have any method of identifying the order of the points?

I assume you want to create a new table for the lines?

You can try something like this, not sure how well it'll work:

create table lines(uid serial primary key, profile text);
select AddGeometryColumn('lines', 'the_geom', -1, 'LINESTRING', 2);

insert into lines(profile, the_geom)
   select profile, ST_LineFromMultiPoint(ST_collect(the_geom))
   from point_table
   group by profile;

This is, of course, untested.

-Andy
Nicolas Ribot | 2 Oct 15:41 2011
Picon

Re: ERROR: invalid input syntax for integer: "POINT(389 418.12 4420549.100044)"

Hi,

There is a missing comma in geomFromText, between the geometry definition and the SRID value:

INSERT INTO pol_p (x, y,the_geom) VALUES (0, 0, GeomFromText('POINT(389418.12 4420549.100044)', -1));

Nicolas

> Hi,
>
> I am trying to insert geometry information into a point table. But when I
> try to execute following query, I am getting " ERROR:  invalid input syntax
> for integer: "POINT(389418.12 4420549.100044)" " message. I cannot get a
> solution from my searches.
> Is there anyone who has solution to this problem?
>
> QUERY :
>
> INSERT INTO pol_p (x, y,the_geom) VALUES (0, 0,
> GeomFromText('POINT(389418.12 4420549.100044)' -1));
>
> ERROR :
>
> ERROR:  invalid input syntax for integer: "POINT(389418.12 4420549.100044)"
>
> Here is my table SQL statement :
>
> CREATE TABLE pol_p
> (
>   gid serial NOT NULL,
>   the_geom geometry(Point,2097151),
>   x double precision DEFAULT 0,
>   y double precision DEFAULT 0,
>   CONSTRAINT pol_p_pkey PRIMARY KEY (gid )
> )
> WITH (
>   OIDS=TRUE
> );
>
>  
> Best regards...
>
> Ayhan Tekgul
>
> _______________________________________________
> postgis-users mailing list
> postgis-users <at> postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>

_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Gmane