Re: [postgis-users] Typmod and triggers

[Paragon Corporation]

Jose, 

slight omission in my last 
> -- to put back constraints on it --
> SELECT populate_geometry_columns('a'::regclass);
> 

I forgot that use type mod is the default now -- so the above should be

SELECT populate_geometry_columns('a'::regclass, false);

To force it to constrain the geometry using constraints instead of typmod.

 Hope that helps,
 Regina
 http://www.postgis.us


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

Re: [postgis-users] Typmod and triggers

[Paragon Corporation]

Jose,

You've demonstrated two things.

One is a bug which I have ticketed here:
http://trac.osgeo.org/postgis/ticket/1320#comment:2

But the other your ability to change the type of a geometry within a trigger
when its defined as a typmod typed geometry in the table, I'm afriad is by
design
and I suspect by design of a lot of databases.  Let me demonstrate with
dates.


--observe in this case it shouldn't matter what I stuff in date since I
always want to return october 10.
-- 
CREATE TABLE test_dates(gid serial primary key, dt date);
CREATE OR REPLACE FUNCTION public.trigtest_dates() RETURNS trigger AS
$$
BEGIN
NEW.dt = '2011-10-10'::date;
RETURN NEW;
END;
$$ language plpgsql
VOLATILE;


CREATE TRIGGER trigtest_date_before
  BEFORE INSERT
  ON public.test_dates
  FOR EACH ROW
  EXECUTE PROCEDURE public.trigtest_dates();

  INSERT INTO test_dates(dt) VALUES ('hello');

ERROR:  invalid input syntax for type date: "hello"
LINE 1:   INSERT INTO test_dates(dt) VALUES ('hello');

If what you wanted to happen were possible -- I'd be able to get away with
this trick.  The reason I can't is because

NEW record holder itself is typed.  So any data that comes even before the
trigger has to abide by the data type constraints
of the table structure or be able to be unambiguously cast to it since its
got to fit in the NEW structure.

Sine NEW.geom in your case can't hold a POLYGON because it is defined as a
typed MULTIPOLYGON type, you can't fix it in the trigger.

---Now what to do --

Sadly I think your best bet is to not use typmod in this case.  So
--to get it back to an unadorned geometry
ALTER TABLE a ALTER COLUMN geom geometry;

-- to put back constraints on it --
SELECT populate_geometry_columns('a'::regclass);

--if it were a completely new table with no data, you'd have to use
AddGeometryColumn (... passing in false for the last arg)
so it doesn't use typmod.

That will get you back to what you were able to do in 1.5.

I'll document this as a know limitation once I figure out where to put that.

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 Jose Carlos Martinez
> Sent: Tuesday, November 22, 2011 1:53 PM
> To: PostGIS Users Discussion
> Subject: [postgis-users] Typmod and triggers
> 
> Hi all,
> Im using PostgreSQL 9.1 and PG 2.0 under MS Windows.
> 
> Lets say I have a tabla A with a geometry column 'geom' of 
> type geometry(MultiPolygon, 23030).
> then I wrote a trigger (BEFORE INSERT ON, FOR EACH ROW) that changes: 
> New.geom:= ST_GeometryN (New.geom, 1).
> 
> After inserting a row:
> insert into A (geom) values ('SRID=23030;MULTIPOLYGON (((0 0, 
> 10 0, 10 10, 0 0)))'::geometry);
> 
> I got a row inserted in this table with a POLYGON type!!
> 
> 
> And Viceversa:
> I wrote a trigger that change: New.geom:= ST_Multi(geom); 
> then I insert:
> insert into A (geom) values ('SRID=23030; POLYGON ((0 0, 10 
> 0, 10 10, 0 0))'::geometry);
> 
> and then PostgreSQL checks again the typmod geometry before 
> the trigger and it is not possible to insert such a row.
> 
> In PG8.4 without typmod everything worked fine.
> 
> If this is the expected behaviour then I think typmod is 
> adding some important limitations that PostGIS didnt have before.
> If not then what Im doing wrong? can i fix it?
> 
> PD: Sorry if this problem was already post in the email 
> list..i couldnt find it though.
> 
> Thanks in advance,
> Best,
> Jose
> 
> 
> 
> 
> 
> ___
> 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] Typmod and triggers

[Jose Carlos Martinez]

Hi all,
Im using PostgreSQL 9.1 and PG 2.0 under MS Windows.

Lets say I have a tabla A with a geometry column 'geom' of type 
geometry(MultiPolygon, 23030).
then I wrote a trigger (BEFORE INSERT ON, FOR EACH ROW) that changes: 
New.geom:= ST_GeometryN (New.geom, 1).


After inserting a row:
insert into A (geom) values ('SRID=23030;MULTIPOLYGON (((0 0, 10 0, 10 
10, 0 0)))'::geometry);


I got a row inserted in this table with a POLYGON type!!


And Viceversa:
I wrote a trigger that change: New.geom:= ST_Multi(geom);
then I insert:
insert into A (geom) values ('SRID=23030; POLYGON ((0 0, 10 0, 10 10, 0 
0))'::geometry);


and then PostgreSQL checks again the typmod geometry before the trigger 
and it is not possible to insert such a row.


In PG8.4 without typmod everything worked fine.

If this is the expected behaviour then I think typmod is adding some 
important limitations that PostGIS didnt have before.

If not then what Im doing wrong? can i fix it?

PD: Sorry if this problem was already post in the email list..i couldnt 
find it though.


Thanks in advance,
Best,
Jose





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

Re: [postgis-users] ST_Mapalgebra

[Bborie Park]

On Tue, Nov 22, 2011 at 9:27 AM, Joan  wrote:
> Hi,
>
> How can I perform ST_MapAlgebraExpr with raster layers that have different
> scale_x, scale_y. I suppose that the difference causes the two raster layers
> to be aligned differently
> Joan

Joan,

You'll need to resample one of the rasters to be aligned with the
other raster.  Something like:

ST_Resample(rast2, rast1)

That'll resample rast2 to be aligned with rast1.  So, you should be
able to use that with ST_MapAlgebraExpr...

ST_MapAlgebraExpr(rast1, ST_Resample(rast2, rast1), ...)

-bborie

-- 
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkp...@ucdavis.edu
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] ST_Mapalgebra

[Joan]

Hi,

How can I perform ST_MapAlgebraExpr with raster layers that have different
scale_x, scale_y. I suppose that the difference causes the two raster
layers to be aligned differently

Joan

On 22 November 2011 17:27, Bborie Park  wrote:

> Joan (and anyone else using 2-raster MapAlgebra),
>
> You don't need to specify ::double precision in your expressions.
> Under the hood, your expression is wrapped with parentheses and
> ::double precision.  So, if your expression was
>
> CASE WHEN rast2 > rast1 THEN rast1 ELSE rast2 END
>
> Internally, the expression becomes
>
> SELECT (CASE WHEN rast2 > rast1 THEN rast1 ELSE rast2 END)::double
> precision
>
> -bborie
>
> On Tue, Nov 22, 2011 at 6:51 AM, Joan  wrote:
> > I have tried it and it works. Thank you Pierre.
> >
> > On 22 November 2011 15:33, Pierre Racine 
> > wrote:
> >>
> >> > I have used ST_MapAlgebraExpr but I get the error below.
> >> >
> >> >
> >> > select * from (select ST_MapAlgebraExpr(kb.rast, kb1.rast,
> >> > 'kb1.rast+kb.rast') as
> >> > rast from kb_dsm_clip kb, kb_dsm_clip100 kb1) as r1 limit 5;
> >> > ERROR: missing FROM-clause entry for table "kb1"
> >> > LINE 1: SELECT (KB1.RAST+KB.RAST)::double precision ^
> >> > QUERY: SELECT (KB1.RAST+KB.RAST)::double precision
> >>
> >> Try this instead:
> >>
> >> SELECT ST_MapAlgebraExpr(kb.rast, kb1.rast, 'rast1 + rast2') as rast
> >> FROM kb_dsm_clip kb, kb_dsm_clip100 kb1
> >> WHERE st_intersects(kb.rast, kb1.rast)
> >>
> >> 1) There is no need for a subquery
> >>
> >> 2) You must refer to the raster using 'rast1' and 'rast2', not their
> real
> >> names
> >>
> >> 3) I don't know if your rasters are tiled but it is always a good idea
> to
> >> restrict the process to intersecting footprints.
> >>
> >> 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
> >
> >
>
>
>
> --
> Bborie Park
> Programmer
> Center for Vectorborne Diseases
> UC Davis
> 530-752-8380
> bkp...@ucdavis.edu
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] ST_Mapalgebra

[Bborie Park]

Joan (and anyone else using 2-raster MapAlgebra),

You don't need to specify ::double precision in your expressions.
Under the hood, your expression is wrapped with parentheses and
::double precision.  So, if your expression was

CASE WHEN rast2 > rast1 THEN rast1 ELSE rast2 END

Internally, the expression becomes

SELECT (CASE WHEN rast2 > rast1 THEN rast1 ELSE rast2 END)::double precision

-bborie

On Tue, Nov 22, 2011 at 6:51 AM, Joan  wrote:
> I have tried it and it works. Thank you Pierre.
>
> On 22 November 2011 15:33, Pierre Racine 
> wrote:
>>
>> > I have used ST_MapAlgebraExpr but I get the error below.
>> >
>> >
>> > select * from (select ST_MapAlgebraExpr(kb.rast, kb1.rast,
>> > 'kb1.rast+kb.rast') as
>> > rast from kb_dsm_clip kb, kb_dsm_clip100 kb1) as r1 limit 5;
>> > ERROR: missing FROM-clause entry for table "kb1"
>> > LINE 1: SELECT (KB1.RAST+KB.RAST)::double precision ^
>> > QUERY: SELECT (KB1.RAST+KB.RAST)::double precision
>>
>> Try this instead:
>>
>> SELECT ST_MapAlgebraExpr(kb.rast, kb1.rast, 'rast1 + rast2') as rast
>> FROM kb_dsm_clip kb, kb_dsm_clip100 kb1
>> WHERE st_intersects(kb.rast, kb1.rast)
>>
>> 1) There is no need for a subquery
>>
>> 2) You must refer to the raster using 'rast1' and 'rast2', not their real
>> names
>>
>> 3) I don't know if your rasters are tiled but it is always a good idea to
>> restrict the process to intersecting footprints.
>>
>> 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
>
>



-- 
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkp...@ucdavis.edu
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] ST_ShortestLine(r.geom, m.geom) confusion - better limiting point to nearest line results

[Chris English]

create table summit_final_drop_8 asselect  Distinct 
ST_ShortestLine(a.intersection_geom, b.geom) from summit_parcels_centroid as b, 
 summit_roads as a where ST_DWithin(a.intersection_geom,b.geom,150) ;
This got me nearly there and preserves the start/end of the line segment needed 
for_pg_routing, though throws up additional or excess final connections, 
probablya limit would help.  At least as viewed in UDig, the segment lengths 
looked right . I'll look into nearest neighbor as an alternative solution.

Date: Tue, 22 Nov 2011 06:19:48 +
From: borntope...@yahoo.co.uk
To: postgis-users@postgis.refractions.net
Subject: Re: [postgis-users] ST_ShortestLine(r.geom,m.geom) confusion - 
better limiting  point to nearest line results

Yes you will as it finds the shortest distance each time between points and 
lines, sorry - you need the inner query to give you the nearest neighbour and 
then generate the line between that and the point.  Have a quick search for 
"nearest neighbour postgis" for how to do this.  That should give you the 
results you are after.
I think I have something somewhere if you can't find it online.
Phil
   Fromris English 
 To: postgis_users  
 Sent: Tuesday, 22 November 2011, 1:09
 Subject: Re: [postgis-users] ST_ShortestLine(r.geom, m.geom) confusion - 
better limiting  point to nearest line results
  









Thanks Phil for your response.  The below does greatly reduce number of rows 
created through theinner join.The "shortestlines' unfortunately seem to radiate 
from the center of town.  
create table summit_final_drop asselect 
ST_ShortestLine(nearestgeom.intersection_geom, b.geom) from 
summit_parcels_centroid as b, (Select a.*, 
ST_Distance(a.intersection_geom, b.geom) as dist from summit_roads as a, 
summit_parcels_centroid as b order by dist asc limit 1) as nearestgeom;Query 
returned successfully with no result in 12281 ms.
Changing asc limit 1) to desc limit 1) and all segments come from the edge of 
as if a fan.
I return to your suggested order ST_Shortestline(point, line) - though I was 
concerned that this will flip my start/end points for the segment with an 
eyetoward the final part, pg_routing
create table summit_final_drop_4 asselect ST_ShortestLine(b.geom, 
nearestgeom.geom) from summit_parcels_centroid as b, (Select a.*, 
ST_Distance(a.intersection_geom, b.geom) as dist from summit_roads as a, 
summit_parcels_centroid as b order by dist asc limit 1) as nearestgeom;
And result is same radiation from the center.
Running St_ShortestLine twice:
create table summit_final_drop_7 asselect ST_ShortestLine(nearestgeom.geom, 
b.geom) from summit_parcels_centroid as b, (Select
 a.*, 
ST_Shortestline(a.intersection_geom, b.geom) as dist from summit_roads as a, 
summit_parcels_centroid as b  order by dist asc limit 1 ) as nearestgeom;
Fan again.  So, something about the order that roads are compared to points.  
I'll keep poking around.Chris

Date: Mon, 21 Nov 2011 22:13:33 +
From: borntope...@yahoo.co.uk
Subject: Re:
 [postgis-users] ST_ShortestLine(r.geom, m.geom) confusion - limiting  point to 
nearest line results
To: postgis-users@postgis.refractions.net; sgl...@hotmail.com

Hi Chris

You need an inner query that selects the closest line segment and use that 
geometry in the shortest line query 

Select shrtest line(p.the_geom,nearestgeom.thegeom) from

Points p,

Select  a.*, st_distance(a.the_geom, b.the_geom) as dist from roads a,points b 
order by dist asc limit 1) as nearestgeom

Hope this is readable as sending from phone.









From:

Chris English ; 
   


To:

 ;   

  


Subject:

[postgis-users] ST_ShortestLine(r.geom, m.geom) 
confusion - limiting  point to nearest line results


Sent:

Mon, Nov 21, 2011 9:00:47 PM













Hi all,
My goal is to model electric distribution in a municipality with 8.4, PostGis 
1.5 and pgrouting.
I clipped roads and parcels from state level (roads) and county level (parcels) 
to get roads and parcels fora municipality resulting in two tables:
summit_roads (306 rows) and su

Re: [postgis-users] ST_Mapalgebra

[Joan]

I have tried it and it works. Thank you Pierre.

On 22 November 2011 15:33, Pierre Racine wrote:

> > I have used ST_MapAlgebraExpr but I get the error below.
> >
> >
> > select * from (select ST_MapAlgebraExpr(kb.rast, kb1.rast,
> 'kb1.rast+kb.rast') as
> > rast from kb_dsm_clip kb, kb_dsm_clip100 kb1) as r1 limit 5;
> > ERROR: missing FROM-clause entry for table "kb1"
> > LINE 1: SELECT (KB1.RAST+KB.RAST)::double precision ^
> > QUERY: SELECT (KB1.RAST+KB.RAST)::double precision
>
> Try this instead:
>
> SELECT ST_MapAlgebraExpr(kb.rast, kb1.rast, 'rast1 + rast2') as rast
> FROM kb_dsm_clip kb, kb_dsm_clip100 kb1
> WHERE st_intersects(kb.rast, kb1.rast)
>
> 1) There is no need for a subquery
>
> 2) You must refer to the raster using 'rast1' and 'rast2', not their real
> names
>
> 3) I don't know if your rasters are tiled but it is always a good idea to
> restrict the process to intersecting footprints.
>
> 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] ST_Mapalgebra

[Pierre Racine]

> I have used ST_MapAlgebraExpr but I get the error below.
> 
> 
> select * from (select ST_MapAlgebraExpr(kb.rast, kb1.rast, 
> 'kb1.rast+kb.rast') as
> rast from kb_dsm_clip kb, kb_dsm_clip100 kb1) as r1 limit 5;
> ERROR: missing FROM-clause entry for table "kb1"
> LINE 1: SELECT (KB1.RAST+KB.RAST)::double precision ^
> QUERY: SELECT (KB1.RAST+KB.RAST)::double precision

Try this instead:

SELECT ST_MapAlgebraExpr(kb.rast, kb1.rast, 'rast1 + rast2') as rast 
FROM kb_dsm_clip kb, kb_dsm_clip100 kb1
WHERE st_intersects(kb.rast, kb1.rast)

1) There is no need for a subquery

2) You must refer to the raster using 'rast1' and 'rast2', not their real names

3) I don't know if your rasters are tiled but it is always a good idea to 
restrict the process to intersecting footprints.

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

Re: [postgis-users] ST_Mapalgebra

[Joan]

Hi Tom,

Thanks for the help, the query now runs. There are no other quotes in the
query.

Regards

Joan

On 22 November 2011 15:16, Tom van Tilburg wrote:

>  Joan,
>
> I think your expression is wrong.
> 'kb1.rast+kb.rast'
> should be:
> 'rast1+rast2'
>
> Oddly enough, I would not expect this error message with that error, so
> I'm curious wether it will make a change. Could it be there is any more
> quotes in the query that we can't see?
>
> Chrs,
>  Tom
>
>
> On 22-11-2011 14:24, Joan wrote:
>
> Hi Bborie,
>
>  I have used ST_MapAlgebraExpr but I get the error below.
>
>  select * from (select ST_MapAlgebraExpr(kb.rast, kb1.rast,
> 'kb1.rast+kb.rast') as rast from kb_dsm_clip kb, kb_dsm_clip100 kb1) as r1
> limit 5;
> ERROR: missing FROM-clause entry for table "kb1"
> LINE 1: SELECT (KB1.RAST+KB.RAST)::double precision
> ^
> QUERY: SELECT (KB1.RAST+KB.RAST)::double precision
>
>  ^
>
>  At what point should I add :: double precision, I added it like this and
> also got an error.
>
>  select * from (select ST_MapAlgebraExpr(kb.rast, kb1.rast,
> 'kb1.rast+kb.rast')::double precision as rast from kb_dsm_clip kb,
> kb_dsm_clip100 kb1) as r1 limit 5;
> ERROR: cannot cast type raster to double precision
> LINE 1: ...AlgebraExpr(kb.rast, kb1.rast, 'kb1.rast+kb.rast')::double p...
> ^
>
>  Regards
>
>  Joan
>
>
>  On 21 November 2011 16:06, Bborie Park  wrote:
>
>>  On Mon, Nov 21, 2011 at 7:02 AM, Joan  wrote:
>> > Hello,
>> >
>> > When I run the following query
>> > select ST_MapAlgebra(r1.rast, r2.rast, "rast1 + rast2") from (select
>> > r1.rast, r2.rast from r1, r2) as foo limit 10;
>> > I get this error
>> > ERROR: column "rast + rast" does not exist
>> > LINE 1: ...t * from (select ST_MapAlgebra(r1.rast, r2.rast, "rast +
>> ra...
>> > ^
>> > What could be the problem? What is the correct syntax for st_mapalgebra?
>> > I have PostGIS installed the version updated on 18th Nov 2011.
>> >
>> > Joan
>>
>>  Hey Joan,
>>
>> You'll want to use ST_MapAlgebraExpr or ST_MapAlgebraFct.
>>
>>
>> http://postgis.refractions.net/documentation/manual-svn/RT_reference.html#Raster_Processing
>>
>> -bborie
>>
>> --
>> Bborie Park
>> Programmer
>> Center for Vectorborne Diseases
>> UC Davis
>> 530-752-8380
>> bkp...@ucdavis.edu
>> ___
>> postgis-users mailing list
>> postgis-users@postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>
>
>
> ___
> postgis-users mailing 
> listpostgis-users@postgis.refractions.nethttp://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] ST_Mapalgebra

[Tom van Tilburg]

Joan,

I think your expression is wrong.
'kb1.rast+kb.rast'
should be:
'rast1+rast2'

Oddly enough, I would not expect this error message with that error, so 
I'm curious wether it will make a change. Could it be there is any more 
quotes in the query that we can't see?


Chrs,
 Tom

On 22-11-2011 14:24, Joan wrote:

Hi Bborie,

I have used ST_MapAlgebraExpr but I get the error below.

select * from (select ST_MapAlgebraExpr(kb.rast, kb1.rast, 
'kb1.rast+kb.rast') as rast from kb_dsm_clip kb, kb_dsm_clip100 kb1) 
as r1 limit 5;

ERROR: missing FROM-clause entry for table "kb1"
LINE 1: SELECT (KB1.RAST+KB.RAST)::double precision
^
QUERY: SELECT (KB1.RAST+KB.RAST)::double precision

^


At what point should I add :: double precision, I added it like this 
and also got an error.


select * from (select ST_MapAlgebraExpr(kb.rast, kb1.rast, 
'kb1.rast+kb.rast')::double precision as rast from kb_dsm_clip kb, 
kb_dsm_clip100 kb1) as r1 limit 5;

ERROR: cannot cast type raster to double precision
LINE 1: ...AlgebraExpr(kb.rast, kb1.rast, 'kb1.rast+kb.rast')::double p...
^

Regards

Joan


On 21 November 2011 16:06, Bborie Park > wrote:


On Mon, Nov 21, 2011 at 7:02 AM, Joan mailto:wamaithaj...@gmail.com>> wrote:
> Hello,
>
> When I run the following query
> select ST_MapAlgebra(r1.rast, r2.rast, "rast1 + rast2") from (select
> r1.rast, r2.rast from r1, r2) as foo limit 10;
> I get this error
> ERROR: column "rast + rast" does not exist
> LINE 1: ...t * from (select ST_MapAlgebra(r1.rast, r2.rast,
"rast + ra...
> ^
> What could be the problem? What is the correct syntax for
st_mapalgebra?
> I have PostGIS installed the version updated on 18th Nov 2011.
>
> Joan

Hey Joan,

You'll want to use ST_MapAlgebraExpr or ST_MapAlgebraFct.


http://postgis.refractions.net/documentation/manual-svn/RT_reference.html#Raster_Processing

-bborie

--
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkp...@ucdavis.edu 
___
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] trouble shp2pgsql with numeric

[Tonton]

Yes it's imported at numeric column but 0 becam 0.00 and i just
need to remove all last 0 after the dot
i hope to keep numeric column for these data but with correct decimal ...

T.

On Tue, Nov 22, 2011 at 2:03 PM, Ben Madin
wrote:

> Tonton,
>
> This is a limitation of shapefiles .dbf format. (yet another reason to
> move away from shapefiles). PostGIS can't assume that your value is a
> categorical value (ie a label) when the column only contains numbers and is
> tagged as numeric. To allow for the widest range of cases, it is imported
> probably as float or numeric, so if all your column values are integer you
> could easily (post-import) issue :
>
> ALTER TABLE ltb ALTER COLUMN annoying_column TYPE int USING
> cast(annoying_column as int);
>
> hth.
>
> cheers
>
> Ben
>
> On 22/11/2011, at 5:20 PM, Tonton wrote:
>
> > hello
> >
> > i use shp2pgsql in a web mapping application to add shp file into
> postgis.
> >
> > after uploading on the server side (in django framwork) i use this cmd
> >
> >  cmd = "/usr/bin/shp2pgsql -c -s 4326 -W latin1 -g geometry
> "+/pathToSHPname+" " +str(tablename)+">"+/pathToMyFile.sql"
> >
> > it is working near great now but for some numeric information  in the
> sql request  numbers are transform to sort of float : 2 became 2.00
> >
> > this is an exemple for my generate sql !
> >
> > INSERT INTO "ltb"
> ("coef_conge","depart_lig","fc","freq_moy","freq__0","freq_moy_m","id_aire","id_station","libbelle","libelle","libell_","ligne","mode","num_type","nom_ligne","nom_statio","num_ligne","num_ligne_","sens","tps_parc","terminus_l","tps_parcou","type_acces","v_com_sytr","vitesse","id_lgn","nom_lgn","nom_stt","order","tpsatt_lgn","vit_off_km","vitcom_lgn","vitesse_km","length",geometry)
> VALUES
> ('0.00',NULL,'104','0.00','0','0.00','0','0',NULL,NULL,NULL,NULL,NULL,'0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0',NULL,'0.00','0','18226',NULL,NULL,'31','0','0.00','0.00','0.00','222.2906997126','010520E6100100010220E6100900E721D9B6305F1340E073BE5D7BF34640319802F44B5F13402462B73D81F34640B40C3CD08B5F1340C2FD98C68BF34640C8A0171BA45F134083D387CD8EF34640DBF078F7D05F13408FF4362192F346404248D110246013400742949695F34640BEA067CCB56013403119CEC098F346407A9371A6FD601340F5DF664A9BF34640EA04C7B160611340602B
>  BB52A1F34640');
> >
> > it is disapointing for me because sometimes the use of these data is for
> label or legend and it is not great to see 2.000 instead of 2 on
> map or in legend part
> >
> > does someone have same trouble or idea to resolv the trouble  ?
> >
> > regards
> >
> > T.
> > ___
> > 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] ST_Mapalgebra

[Joan]

Hi Bborie,

I have used ST_MapAlgebraExpr but I get the error below.

select * from (select ST_MapAlgebraExpr(kb.rast, kb1.rast,
'kb1.rast+kb.rast') as rast from kb_dsm_clip kb, kb_dsm_clip100 kb1) as r1
limit 5;
ERROR: missing FROM-clause entry for table "kb1"
LINE 1: SELECT (KB1.RAST+KB.RAST)::double precision
^
QUERY: SELECT (KB1.RAST+KB.RAST)::double precision

 ^

At what point should I add :: double precision, I added it like this and
also got an error.

select * from (select ST_MapAlgebraExpr(kb.rast, kb1.rast,
'kb1.rast+kb.rast')::double precision as rast from kb_dsm_clip kb,
kb_dsm_clip100 kb1) as r1 limit 5;
ERROR: cannot cast type raster to double precision
LINE 1: ...AlgebraExpr(kb.rast, kb1.rast, 'kb1.rast+kb.rast')::double p...
^

Regards

Joan


On 21 November 2011 16:06, Bborie Park  wrote:

> On Mon, Nov 21, 2011 at 7:02 AM, Joan  wrote:
> > Hello,
> >
> > When I run the following query
> > select ST_MapAlgebra(r1.rast, r2.rast, "rast1 + rast2") from (select
> > r1.rast, r2.rast from r1, r2) as foo limit 10;
> > I get this error
> > ERROR: column "rast + rast" does not exist
> > LINE 1: ...t * from (select ST_MapAlgebra(r1.rast, r2.rast, "rast + ra...
> > ^
> > What could be the problem? What is the correct syntax for st_mapalgebra?
> > I have PostGIS installed the version updated on 18th Nov 2011.
> >
> > Joan
>
> Hey Joan,
>
> You'll want to use ST_MapAlgebraExpr or ST_MapAlgebraFct.
>
>
> http://postgis.refractions.net/documentation/manual-svn/RT_reference.html#Raster_Processing
>
> -bborie
>
> --
> Bborie Park
> Programmer
> Center for Vectorborne Diseases
> UC Davis
> 530-752-8380
> bkp...@ucdavis.edu
> ___
> 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] (no subject)

[joel ml]

http://duquedemar.es/modules/mod_wdbanners/test.php?html143___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] trouble shp2pgsql with numeric

[Ben Madin]

Tonton,

This is a limitation of shapefiles .dbf format. (yet another reason to move 
away from shapefiles). PostGIS can't assume that your value is a categorical 
value (ie a label) when the column only contains numbers and is tagged as 
numeric. To allow for the widest range of cases, it is imported probably as 
float or numeric, so if all your column values are integer you could easily 
(post-import) issue :

ALTER TABLE ltb ALTER COLUMN annoying_column TYPE int USING 
cast(annoying_column as int);

hth.

cheers

Ben

On 22/11/2011, at 5:20 PM, Tonton wrote:

> hello 
> 
> i use shp2pgsql in a web mapping application to add shp file into postgis. 
> 
> after uploading on the server side (in django framwork) i use this cmd
> 
>  cmd = "/usr/bin/shp2pgsql -c -s 4326 -W latin1 -g geometry 
> "+/pathToSHPname+" " +str(tablename)+">"+/pathToMyFile.sql"
> 
> it is working near great now but for some numeric information  in the sql 
> request  numbers are transform to sort of float : 2 became 2.00
> 
> this is an exemple for my generate sql ! 
> 
> INSERT INTO "ltb" 
> ("coef_conge","depart_lig","fc","freq_moy","freq__0","freq_moy_m","id_aire","id_station","libbelle","libelle","libell_","ligne","mode","num_type","nom_ligne","nom_statio","num_ligne","num_ligne_","sens","tps_parc","terminus_l","tps_parcou","type_acces","v_com_sytr","vitesse","id_lgn","nom_lgn","nom_stt","order","tpsatt_lgn","vit_off_km","vitcom_lgn","vitesse_km","length",geometry)
>  VALUES 
> ('0.00',NULL,'104','0.00','0','0.00','0','0',NULL,NULL,NULL,NULL,NULL,'0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0',NULL,'0.00','0','18226',NULL,NULL,'31','0','0.00','0.00','0.00','222.2906997126','010520E6100100010220E6100900E721D9B6305F1340E073BE5D7BF34640319802F44B5F13402462B73D81F34640B40C3CD08B5F1340C2FD98C68BF34640C8A0171BA45F134083D387CD8EF34640DBF078F7D05F13408FF4362192F346404248D110246013400742949695F34640BEA067CCB56013403119CEC098F346407A9371A6FD601340F5DF664A9BF34640EA04C7B160611340602B
 BB52A1F34640');
> 
> it is disapointing for me because sometimes the use of these data is for 
> label or legend and it is not great to see 2.000 instead of 2 on map 
> or in legend part
> 
> does someone have same trouble or idea to resolv the trouble  ? 
> 
> regards 
> 
> T. 
> ___
> 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] Topology improvements, testing, funding

[Sandro Santilli]

On Mon, Nov 21, 2011 at 07:34:04PM +0100, Sandro Santilli wrote:

> The hardest step for you to test use of TopoGeometry objects so far would
> be converting your Geometry layers to TopoGeometry layers. This is what
> ticket #1017 is willing to address [1]. The ticket is open looking for
> co-funders.

FYI: I made a pledge:

<< I will implement a Geometry to TopoGeometry importer in PostGIS-2.0
   but only if 10 other people will donate 250 euro. >>

Deadline to sign up is December 20th, 2011.
Let's see if PostGIS users can do it again :)

Sign up on http://www.pledgebank.com/postgistopology

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Build topology for polygon layer using postgis2.0

[Sandro Santilli]

On Thu, Nov 03, 2011 at 04:26:26PM +0100, Sandro Santilli wrote:
> On Thu, Nov 03, 2011 at 07:09:59AM -0700, Elton Chan wrote:

> > ERROR:  SQL/MM Spatial exception - non-existent node
> > CONTEXT:  SQL statement "SELECT topology.ST_AddEdgeModFace(atopology, 
> > snode_id, enode_id, rec.geom)"
> 
> This is unexpected.
> Can you share your input collection to take a look ?
> Best if you can shrink it as much as possible.

Just an heads-up on this: Elton dataset helped a lot making the
postgis topology code more robust. I hope this experience would
take others to follow the example and take their time to prepare
interesting/problematic datasets for the developers to play with.

The outcome of this experience here:
http://strk.keybit.net/blog/2011/11/21/topology-cleaning-with-postgis/

--strk; 

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] trouble shp2pgsql with numeric

[Tonton]

hello

i use shp2pgsql in a web mapping application to add shp file into postgis.

after uploading on the server side (in django framwork) i use this cmd

 cmd = "/usr/bin/shp2pgsql -c -s 4326 -W latin1 -g geometry
"+/pathToSHPname+" " +str(tablename)+">"+/pathToMyFile.sql"

it is working near great now but for some numeric information  in the sql
request  numbers are transform to sort of float : 2 became 2.00

this is an exemple for my generate sql !

INSERT INTO "ltb"
("coef_conge","depart_lig","fc","freq_moy","freq__0","freq_moy_m","id_aire","id_station","libbelle","libelle","libell_","ligne","mode","num_type","nom_ligne","nom_statio","num_ligne","num_ligne_","sens","tps_parc","terminus_l","tps_parcou","type_acces","v_com_sytr","vitesse","id_lgn","nom_lgn","nom_stt","order","tpsatt_lgn","vit_off_km","vitcom_lgn","vitesse_km","length",geometry)
VALUES
('0.00',NULL,'104','0.00','0','0.00','0','0',NULL,NULL,NULL,NULL,NULL,'0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0',NULL,'0.00','0','18226',NULL,NULL,'31','0','0.00','0.00','0.00','222.2906997126','010520E6100100010220E6100900E721D9B6305F1340E073BE5D7BF34640319802F44B5F13402462B73D81F34640B40C3CD08B5F1340C2FD98C68BF34640C8A0171BA45F134083D387CD8EF34640DBF078F7D05F13408FF4362192F346404248D110246013400742949695F34640BEA067CCB56013403119CEC098F346407A9371A6FD601340F5DF664A9BF34640EA04C7B160611340602BBB52A1F34640');

it is disapointing for me because sometimes the use of these data is for
label or legend and it is not great to see 2.000 instead of 2 on
map or in legend part

does someone have same trouble or idea to resolv the trouble  ?

regards

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