Re: [postgis-users] Error on updating geometry column from two columns value

2011-12-01 Thread Firman Hadi 

Dear Mike,

Thank you for the solutions. I will try it.
Thanks again.

Cheers,

Firman.

On 12/2/11 5:16 AM, Mike Toews wrote:

ST_GeomFromText turns WKT into a geometry, not SQL. You can either
format WKT (text) using:

ST_GeomFromText('POINT(' || NEW.koordinat_x || ', ' || NEW.koordinat_y
|| ')', 32748)

Or, a simpler/faster/lossless geometry constructor would be to pass
the floating point values directly to a point geometry:

ST_SetSRID(ST_MakePoint(NEW.koordinat_x, NEW.koordinat_y), 32748)

Note: you had your X/Y coordinates the other way around. PostGIS
coordinates are always ordered X/Y or long/lat.

-Mike

On 1 December 2011 20:31, Firman Hadi  wrote:

Dear all,

I want to create one table with 3 columns (x, y, geom). I want to input the
x and y using form with PHP.
When I submit the form, it will trigger the new row.

I use this step below but when I insert the data I get the error as in
attachment.

I hope that anyone can help me to solve the problem.

Thank you in advance.

Kind regards,

Firman Hadi
Center for Remote Sensing - ITB
Indonesia




CREATE TABLE try_geometry (
 koordinat_y integer,
 koordinat_x integer,
 geom geometry PRIMARY KEY
  );


CREATE FUNCTION try_geometry_func () RETURNS trigger AS '
  BEGIN
 NEW.geometri = ST_GeomFromText('POINT(NEW.koordinat_y,
NEW.koordinat_x)',32748);
 RETURN NEW;
  END;
  ' LANGUAGE plpgsql;

===

CREATE TRIGGER try_geom_trg BEFORE INSERT OR UPDATE
 ON coba FOR EACH ROW
 EXECUTE PROCEDURE try_geometry_func ();

___
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] Error on updating geometry column from two columns value

2011-12-01 Thread Mike Toews 
ST_GeomFromText turns WKT into a geometry, not SQL. You can either
format WKT (text) using:

ST_GeomFromText('POINT(' || NEW.koordinat_x || ', ' || NEW.koordinat_y
|| ')', 32748)

Or, a simpler/faster/lossless geometry constructor would be to pass
the floating point values directly to a point geometry:

ST_SetSRID(ST_MakePoint(NEW.koordinat_x, NEW.koordinat_y), 32748)

Note: you had your X/Y coordinates the other way around. PostGIS
coordinates are always ordered X/Y or long/lat.

-Mike

On 1 December 2011 20:31, Firman Hadi  wrote:
> Dear all,
>
> I want to create one table with 3 columns (x, y, geom). I want to input the
> x and y using form with PHP.
> When I submit the form, it will trigger the new row.
>
> I use this step below but when I insert the data I get the error as in
> attachment.
>
> I hope that anyone can help me to solve the problem.
>
> Thank you in advance.
>
> Kind regards,
>
> Firman Hadi
> Center for Remote Sensing - ITB
> Indonesia
>
>
>
>
> CREATE TABLE try_geometry (
>     koordinat_y integer,
>     koordinat_x integer,
>     geom geometry PRIMARY KEY
>  );
> 
>
> CREATE FUNCTION try_geometry_func () RETURNS trigger AS '
>  BEGIN
>     NEW.geometri = ST_GeomFromText('POINT(NEW.koordinat_y,
> NEW.koordinat_x)',32748);
>     RETURN NEW;
>  END;
>  ' LANGUAGE plpgsql;
>
> ===
>
> CREATE TRIGGER try_geom_trg BEFORE INSERT OR UPDATE
>     ON coba FOR EACH ROW
>     EXECUTE PROCEDURE try_geometry_func ();
>
> ___
> 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 error - current transaction is aborted...

2011-12-01 Thread jdmorgan 

On 12/1/2011 9:56 AM, Andy Colson wrote:

On 12/1/2011 8:04 AM, jdmorgan wrote:

Hello,

I am attempting utilizing the shp2pgsql utility to import a
shapefile.The utility reports that the shapefile is of the type
MULTIPOLYGON[2] and then proceeds to through a bunch of errors for each
record that it can’t import “current transaction is aborted, commands
ignored until end of transaction block”.Any advice on how to get around
this would be greatly appreciated.

Sincerely,

-- JDM @ NEMAC


If you are using -S  (capital S) (Generate simple geometries instead 
of MULTI geometries) then that would be a problem.


Showing us your command might help.

-Andy



Here is the command I was using  shp2pgsql -s 4326 
FHM_ADS_2003_4326.shp  public.FHM_ADS_2003 | psql -d pgtest1 -U mbp
I have figured out the issue by looking at the SQL generated by 
shp2pgsql.  As it turns out there were null in a date field in the shape 
file, and postgresql didn't like that for the insert statement.


Thanks,

--
JDM Morgan @ NEMAC

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

Re: [postgis-users] ERROR: missing FROM-clause entry for table

2011-12-01 Thread Andy Colson 

On 12/1/2011 7:29 AM, maduako ikechukwu wrote:

Hi guys,
  please could someone help me look at this SQL code, I cant figure out
what is wrong with it and it gives me this error,

"ERROR:  missing FROM-clause entry for table "i"
LINE 3: Select I.temp_lst_id,I.temp_value as I_t, R.tv.val as R_t, (..."
^
Here is the code;

Select I.temp_value as I_t, R.tv.val as R_t, (I_t - R_t) as D_t
 From ( Select ST_intersection(R.rast,I.the_geom) AS tv
 From in_situ_lst I, lst_day R
Where I.the_geom && R.rast
AND ST_intersects(R.rast,I.the_geom)
And I.temp_lst_id = 2
)foo;


There is an outside statement, and an inside.  The outside:
Select I.temp_value as I_t, R.tv.val as R_t, (I_t - R_t) as D_t

is trying to use a table alias from the inside.  You can't do that.

Or said another way, you are using derived tables.  The inside select 
statement is run first, and the resultset is called foo:


Select ST_intersection(R.rast,I.the_geom) AS tv
From in_situ_lst I, lst_day R
Where I.the_geom && R.rast
AND ST_intersects(R.rast,I.the_geom)
And I.temp_lst_id = 2

The outside then does "select [stuff] from foo".  The outside cannot see 
any table's named I.  It can only see foo.


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

Re: [postgis-users] shp2pgsql error - current transaction is aborted...

2011-12-01 Thread Andy Colson 

On 12/1/2011 8:04 AM, jdmorgan wrote:

Hello,

I am attempting utilizing the shp2pgsql utility to import a
shapefile.The utility reports that the shapefile is of the type
MULTIPOLYGON[2] and then proceeds to through a bunch of errors for each
record that it can’t import “current transaction is aborted, commands
ignored until end of transaction block”.Any advice on how to get around
this would be greatly appreciated.

Sincerely,

-- JDM @ NEMAC


If you are using -S  (capital S) (Generate simple geometries instead of 
MULTI geometries) then that would be a problem.


Showing us your command might help.

-Andy

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

Re: [postgis-users] pgsql2shp

2011-12-01 Thread toni hernández 

Mark,

No, I haven't.
Right now I am preparing some material for a course and I need some 
stable release but I'll keep that  in mind.

Thanks.

On 01/12/2011 11:50, Mark Cave-Ayland wrote:

It's had a fairly hefty rewrite

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

Re: [postgis-users] Raster pixel value

2011-12-01 Thread Andreas Forø Tollefsen 
This is great! Fantastic. I will do some testing.

2011/12/1 Tom van Tilburg 

> Pierre,
>
> This is great you're working on this. It is exactly what I think that
> pgraster is going to make a great tool.
>
> Last days I didn't really have time to check out things, hopefully this
> week.
>
> Cheers,
>  Tom
>
>
> On 30-11-2011 19:19, Pierre Racine wrote:
>
>> Andreas, Tom,
>>
>> I have put two new functions you might be interested by in
>>
>> http://trac.osgeo.org/postgis/**browser/trunk/raster/scripts/**plpgsql
>>
>> The first one, st_areaweightedsummarystats.**sql, is usefull when you
>> want to compute summary stats (like the weighted mean) of values coming
>> from a raster table for a series of polygons and that you use
>> ST_Intersection(raster, geometry) to compute the intersection. You normally
>> use it this way:
>>
>> SELECT gt.id,
>>(aws).count,
>>(aws).distinctcount,
>>(aws).geom,
>>(aws).totalarea,
>>(aws).meanarea,
>>(aws).totalperimeter,
>>(aws).meanperimeter,
>>(aws).weightedsum,
>>(aws).weightedmean,
>>(aws).maxareavalue,
>>(aws).minareavalue,
>>(aws).maxcombinedareavalue,
>>(aws).mincombinedareavalue,
>>(aws).sum,
>>(aws).mean,
>>(aws).max,
>>(aws).min
>> FROM (SELECT ST_AreaWeightedSummaryStats(**gv) aws
>>   FROM (SELECT ST_Intersection(rt.rast, gt.geom) gv
>> FROM rasttable rt, geomtable gt
>> WHERE ST_Intersects(rt.rast, gt.geom)
>>) foo
>>   GROUP BY gt.id
>>  ) foo2
>>
>> The second one, st_summarystatsagg.sql, serve the same purpose but when
>> you are using ST_MapAlgebra() (soon ST_Clip()) to compute the intersection.
>> you normally use it like this:
>>
>> SELECT (ss).count,
>>(ss).sum,
>>(ss).mean,
>>(ss).min,
>>(ss).max
>> FROM (SELECT ST_SummaryStatsAgg(gv) ss
>>   FROM (SELECT ST_Clip(rt.rast, gt.geom) gv
>> FROM rasttable rt, geomtable gt
>> WHERE ST_Intersects(rt.rast, gt.geom)
>>) foo
>>   GROUP BY gt.id
>>  ) foo2
>>
>> This is very equivalent to what you were doing. The ST_Clip() replace the
>> ST_MapAlgebra() part and the ST_SummaryStatsAgg() replace the
>> ST_SummaryStats(ST_Union())  part. This should be faster since it does a
>> ST_Union() less.
>>
>> I'm working on a plpgsql version of ST_Clip(). I will post it very soon.
>>
>> 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
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Batch geocoding (~2 mil addresses)

2011-12-01 Thread Stephen Woodbridge 
FYI. You can not do a COMMIT in a plpgsql or any stored procedures 
because the the function call is run in a transaction. The way to do 
this is in an external script like Perl or PHP where you cycle through 
500-1000 addresses and updates and then do the COMMIT.


-Steve

On 12/1/2011 9:04 AM, Ravi ada wrote:

Thanks all for the suggestions. I am migrating this process to a bigger
machine which has 16GB RAM and faster hard drives. I will incorporate these
changes once the migration completes.

There are two things I got so far I need to do.
1) use pgScript to commit every 500 or 1000 updates
2) send explain analyze to http://explain.depesz.com/ further evaluate the
query plan

I will keep you posted guys..

Thanks
Ravi Ada

-Original Message-
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Andy
Colson
Sent: Wednesday, November 30, 2011 8:54 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Batch geocoding (~2 mil addresses)

On 11/29/2011 11:18 PM, Ravi ada wrote:

Hello All,

I have been fighting with PostGIS for the last week or two to geocode
about 2 million addresses. Here are the challenges that I am facing.

1)I sorted the addresses by zip, address, city to group the similar
addresses together.

2)So far I have seen only 50% of rating zero, that means perfect match.

3)Some addresses have Suite# or Apt# in address line 1. Geocode
function takes longer time for such addresses

4)Addresses that are on the interstate, may be on the service road.
These are taking longer to code.


How about posting an "EXPLAIN ANALYZE" to http://explain.depesz.com/.
Maybe we can speed it up.

-Andy
___
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] Geocoding cross streets?

2011-12-01 Thread Aren Cambre 
My question is already answered: someone committed changes at
http://trac.osgeo.org/postgis/ticket/1333#comment:3.

Are these already in the SVN trunk?

Aren

On Wed, Nov 30, 2011 at 6:29 PM, Aren Cambre  wrote:

> Based on the conversation below, it appears there is already a solution,
> and people well-versed with PostGIS may be helping with this already. Given
> that, are my n00b skillz of much use?
>
> Second question--when might this cross street feature be available? I am
> asking for selfish reasons--I am working on a praxis and have thousands of
> cross streets I need to geocode.
>
> Aren
>
>
> On Tue, Nov 29, 2011 at 12:03 PM, Stephen Woodbridge <
> wood...@swoodbridge.com> wrote:
>
>> On 11/29/2011 12:42 PM, Stephen Frost wrote:
>>
>>> * Stephen Woodbridge (wood...@swoodbridge.com) wrote:
>>>
 I currently have some lists of names that are converted to optimized
 pcre regular expressions. I uses these to help separate the street
 from the city name. The lists are only used to create header files
 that contain the regular expressions that get compiled into the
 code. The idea being that these names are reasonably static for a
 given data set.

>>>
>>> Ah, ok, I see.  When converting this to a PG function, I'd probably want
>>> to go ahead and pull those lists from the TIGER data set and compile the
>>> regexps on PG backend startup instead.  Does it handle misspelled names
>>> or do any kind of "sounds like" searching on the city names?  I'm
>>> guessing 'no', but figured I'd ask anyway..
>>>
>>
>> The lists that I have generated are pulled from a number of sources, like
>> the actual tiger data, the fips 4-2 placenames, I also have some common
>> abbreviations, and misspellings, but it is not doing any sounds like
>> searching. I think that I broke the regular expressions into separate state
>> specific regular expressions because putting them all into a sine
>> expression exceeded some limit in pcre.
>>
>> The regex expressions are created in perl and are highly optimized. You
>> probably can not read the regex's and make much sense out of them, but they
>> are extremely efficient to evaluate.
>>
>> Also you can take just that directory from PAGC and build it and it
>> should create a command line executable that you can test with and run it
>> in the debugger and valgrind, etc. Something like:
>>
>> cd parseaddress
>> ./configure
>> make
>> ./parseaddress 101 W MLK AVE NORTH CHELMSFORD MA 01863
>>
>>
>> -Steve
>> __**_
>> 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] Raster pixel value

2011-12-01 Thread Pierre Racine 
I have put a first version of ST_Clip in script/plpgsql

Pierre

> -Original Message-
> From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-
> boun...@postgis.refractions.net] On Behalf Of Tom van Tilburg
> Sent: Thursday, December 01, 2011 7:14 AM
> To: postgis-users@postgis.refractions.net
> Subject: Re: [postgis-users] Raster pixel value
> 
> Pierre,
> 
> This is great you're working on this. It is exactly what I think that 
> pgraster is
> going to make a great tool.
> 
> Last days I didn't really have time to check out things, hopefully this week.
> 
> Cheers,
>   Tom
> 
> On 30-11-2011 19:19, Pierre Racine wrote:
> > Andreas, Tom,
> >
> > I have put two new functions you might be interested by in
> >
> > http://trac.osgeo.org/postgis/browser/trunk/raster/scripts/plpgsql
> >
> > The first one, st_areaweightedsummarystats.sql, is usefull when you want
> to compute summary stats (like the weighted mean) of values coming from
> a raster table for a series of polygons and that you use
> ST_Intersection(raster, geometry) to compute the intersection. You
> normally use it this way:
> >
> > SELECT gt.id,
> > (aws).count,
> > (aws).distinctcount,
> > (aws).geom,
> > (aws).totalarea,
> > (aws).meanarea,
> > (aws).totalperimeter,
> > (aws).meanperimeter,
> > (aws).weightedsum,
> > (aws).weightedmean,
> > (aws).maxareavalue,
> > (aws).minareavalue,
> > (aws).maxcombinedareavalue,
> > (aws).mincombinedareavalue,
> > (aws).sum,
> > (aws).mean,
> > (aws).max,
> > (aws).min
> > FROM (SELECT ST_AreaWeightedSummaryStats(gv) aws
> >FROM (SELECT ST_Intersection(rt.rast, gt.geom) gv
> >  FROM rasttable rt, geomtable gt
> >  WHERE ST_Intersects(rt.rast, gt.geom)
> > ) foo
> >GROUP BY gt.id
> >   ) foo2
> >
> > The second one, st_summarystatsagg.sql, serve the same purpose but
> when you are using ST_MapAlgebra() (soon ST_Clip()) to compute the
> intersection. you normally use it like this:
> >
> > SELECT (ss).count,
> > (ss).sum,
> > (ss).mean,
> > (ss).min,
> > (ss).max
> > FROM (SELECT ST_SummaryStatsAgg(gv) ss
> >FROM (SELECT ST_Clip(rt.rast, gt.geom) gv
> >  FROM rasttable rt, geomtable gt
> >  WHERE ST_Intersects(rt.rast, gt.geom)
> > ) foo
> >GROUP BY gt.id
> >   ) foo2
> >
> > This is very equivalent to what you were doing. The ST_Clip() replace the
> ST_MapAlgebra() part and the ST_SummaryStatsAgg() replace the
> ST_SummaryStats(ST_Union())  part. This should be faster since it does a
> ST_Union() less.
> >
> > I'm working on a plpgsql version of ST_Clip(). I will post it very soon.
> >
> > 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
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] shp2pgsql error - current transaction is aborted...

2011-12-01 Thread jdmorgan 

Hello,

I am attempting utilizing the shp2pgsql utility to import a 
shapefile.The utility reports that the shapefile is of the type 
MULTIPOLYGON[2] and then proceeds to through a bunch of errors for each 
record that it can't import "current transaction is aborted, commands 
ignored until end of transaction block".Any advice on how to get around 
this would be greatly appreciated.


Sincerely,

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

Re: [postgis-users] Batch geocoding (~2 mil addresses)

2011-12-01 Thread Ravi ada 
Thanks all for the suggestions. I am migrating this process to a bigger
machine which has 16GB RAM and faster hard drives. I will incorporate these
changes once the migration completes.

There are two things I got so far I need to do. 
1) use pgScript to commit every 500 or 1000 updates 
2) send explain analyze to http://explain.depesz.com/ further evaluate the
query plan

I will keep you posted guys..

Thanks
Ravi Ada

-Original Message-
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Andy
Colson
Sent: Wednesday, November 30, 2011 8:54 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Batch geocoding (~2 mil addresses)

On 11/29/2011 11:18 PM, Ravi ada wrote:
> Hello All,
>
> I have been fighting with PostGIS for the last week or two to geocode 
> about 2 million addresses. Here are the challenges that I am facing.
>
> 1)I sorted the addresses by zip, address, city to group the similar 
> addresses together.
>
> 2)So far I have seen only 50% of rating zero, that means perfect match.
>
> 3)Some addresses have Suite# or Apt# in address line 1. Geocode 
> function takes longer time for such addresses
>
> 4)Addresses that are on the interstate, may be on the service road.
> These are taking longer to code.

How about posting an "EXPLAIN ANALYZE" to http://explain.depesz.com/. 
Maybe we can speed it up.

-Andy
___
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] ERROR: missing FROM-clause entry for table

2011-12-01 Thread Pierre Racine 
Try this:

SELECT I_t,
   (tv).val as R_t,
   (I_t - R_t) as D_t 
FROM (SELECT ST_intersection(R.rast, I.the_geom) AS tv,
  I.temp_value as I_t
 FROM in_situ_lst I, lst_day R 
 WHERE I.the_geom && R.rast AND ST_intersects(R.rast,I.the_geom) 
And I.temp_lst_id = 2
   ) foo;

You don't have to put I.the_geom && R.rast in the WHERE...

However if your vector table is a table of point you should use ST_Value 
instead of ST_Intersection. If your table is of polygon ST_Intersection might 
return many values for one polygon.

Pierre

> -Original Message-
> From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-
> boun...@postgis.refractions.net] On Behalf Of maduako ikechukwu
> Sent: Thursday, December 01, 2011 8:30 AM
> To: postgis-users@postgis.refractions.net
> Subject: [postgis-users] ERROR: missing FROM-clause entry for table
> 
> Hi guys,
>  please could someone help me look at this SQL code, I cant figure out what
> is wrong with it and it gives me this error,
> 
> "ERROR:  missing FROM-clause entry for table "i"
> LINE 3: Select I.temp_lst_id,I.temp_value as I_t, R.tv.val as R_t, (..."
>^
> Here is the code;
> 
> Select I.temp_value as I_t, R.tv.val as R_t, (I_t - R_t) as D_t From ( Select
> ST_intersection(R.rast,I.the_geom) AS tv From in_situ_lst I, lst_day R
> Where I.the_geom && R.rast AND ST_intersects(R.rast,I.the_geom) And
> I.temp_lst_id = 2 )foo;
> 
> I was just trying to get the difference between a temperature value in a
> vector temperature table, in_situ_lst and a raster temperature table,
> lst_day.
> 
> Regards
> --
> 
> Iyke Maduako
> 
> Masters in Geospatial Technologies
> 
> Institute for Geoinformatics,IfGI
> 
> University of Muenster Germany
> 
> Phone: +4915129048460
> 
> Alternative Email:iykefirstcl...@yahoo.com
> 
> 
> 

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

[postgis-users] ERROR: missing FROM-clause entry for table

2011-12-01 Thread maduako ikechukwu 
Hi guys,
 please could someone help me look at this SQL code, I cant figure out what
is wrong with it and it gives me this error,

"ERROR:  missing FROM-clause entry for table "i"
LINE 3: Select I.temp_lst_id,I.temp_value as I_t, R.tv.val as R_t, (..."
   ^
Here is the code;

Select I.temp_value as I_t, R.tv.val as R_t, (I_t - R_t) as D_t
>From ( Select ST_intersection(R.rast,I.the_geom) AS tv
>From in_situ_lst I, lst_day R
Where I.the_geom && R.rast
AND ST_intersects(R.rast,I.the_geom)
And I.temp_lst_id = 2
)foo;

I was just trying to get the difference between a temperature value in a
vector temperature table, in_situ_lst and a raster temperature table,
lst_day.

Regards
-- 
Iyke Maduako

Masters in Geospatial Technologies
Institute for Geoinformatics,IfGI
University of Muenster Germany
Phone: +4915129048460
Alternative Email:iykefirstcl...@yahoo.com
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Raster pixel value

2011-12-01 Thread Tom van Tilburg 

Pierre,

This is great you're working on this. It is exactly what I think that 
pgraster is going to make a great tool.


Last days I didn't really have time to check out things, hopefully this 
week.


Cheers,
 Tom

On 30-11-2011 19:19, Pierre Racine wrote:

Andreas, Tom,

I have put two new functions you might be interested by in

http://trac.osgeo.org/postgis/browser/trunk/raster/scripts/plpgsql

The first one, st_areaweightedsummarystats.sql, is usefull when you want to 
compute summary stats (like the weighted mean) of values coming from a raster 
table for a series of polygons and that you use ST_Intersection(raster, 
geometry) to compute the intersection. You normally use it this way:

SELECT gt.id,
(aws).count,
(aws).distinctcount,
(aws).geom,
(aws).totalarea,
(aws).meanarea,
(aws).totalperimeter,
(aws).meanperimeter,
(aws).weightedsum,
(aws).weightedmean,
(aws).maxareavalue,
(aws).minareavalue,
(aws).maxcombinedareavalue,
(aws).mincombinedareavalue,
(aws).sum,
(aws).mean,
(aws).max,
(aws).min
FROM (SELECT ST_AreaWeightedSummaryStats(gv) aws
   FROM (SELECT ST_Intersection(rt.rast, gt.geom) gv
 FROM rasttable rt, geomtable gt
 WHERE ST_Intersects(rt.rast, gt.geom)
) foo
   GROUP BY gt.id
  ) foo2

The second one, st_summarystatsagg.sql, serve the same purpose but when you are 
using ST_MapAlgebra() (soon ST_Clip()) to compute the intersection. you 
normally use it like this:

SELECT (ss).count,
(ss).sum,
(ss).mean,
(ss).min,
(ss).max
FROM (SELECT ST_SummaryStatsAgg(gv) ss
   FROM (SELECT ST_Clip(rt.rast, gt.geom) gv
 FROM rasttable rt, geomtable gt
 WHERE ST_Intersects(rt.rast, gt.geom)
) foo
   GROUP BY gt.id
  ) foo2

This is very equivalent to what you were doing. The ST_Clip() replace the 
ST_MapAlgebra() part and the ST_SummaryStatsAgg() replace the 
ST_SummaryStats(ST_Union())  part. This should be faster since it does a 
ST_Union() less.

I'm working on a plpgsql version of ST_Clip(). I will post it very soon.

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] pgsql2shp

2011-12-01 Thread Mark Cave-Ayland 

On 01/12/11 08:32, toni hernández wrote:


Hi,

I have some issues with pgsql2shp.

I am using postgis 1.5.3 over postgresql 9.0 on windows.
The database I am working with is utf8.

I have several spatial tables and I want some of them to be exported to
a shape file.
pgsql2shp seems to be working fine but when I load the generated shape
files, then they appear to be empty. No error thrown (on OpenJump).

I have been testing it for a while and I came to realize that pgsql2shp
does not like tables that only have one field (the spatial one). I can
not export a table (any) with only one column but if I add a column to
that very table (type character varying) then the exportation works fine
and I can load the shp file perfectly

At first I thought it was something about the encoding but now I am lost.

Any ideas how to solve this?
thanks


Hi Toni,

I don't suppose you've been able to try pgsql2shp from one of the 
PostGIS 2.0 pre-releases at all? It's had a fairly hefty rewrite and so 
you might find that this will have solved the issue.



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] bmd.hasnodata does not exist

2011-12-01 Thread Paragon Corporation 
Andreas ,
You don't need to install postgis.sql, rtpostgis.sql
 
Just _upgrade_20_minor.sql ones.
 
That is unless we change the on disk format or change non-droppable things
like casts which we might before release.



  _  

From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Andreas
Forø Tollefsen
Sent: Wednesday, November 30, 2011 12:30 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] bmd.hasnodata does not exist


Thanks! 

After updating to latest revision i only ran: 
psql -d database -f postgis.sql
psql -d database -f rtpostgis.sql

I was not aware that i had to do the _upgrade_20_minor.sql as well.

Now it works.

Andreas


2011/11/30 Bborie Park 


Andreas,

Though you upgraded the underlying library, did you run
rtpostgis_upgrade_20_minor.sql?  Something is definitely out of sync.

-bborie


On Tue, Nov 29, 2011 at 12:58 AM, Andreas Forø Tollefsen
 wrote:
> No one else had the same issue with 8242? This is exactly the same query
as
> i ran in r.8001, but now it give me the error below in return.
> Any suggestions?
> Best,
> Andreas
>
> 2011/11/25 Andreas Forø Tollefsen 
>>
>> Hi,
>> Sorry for posting a lot these days but I am very busy finalizing a
>> project.
>> Previously, I have been running the following query without problems (in
>> rev.8001). After updating to rev.8242 i am getting a strange error.
>> Could someone please see what is wrong? Is it one of the functions that
>> has changed?
>> Query:
>> SELECT gid, AVG(((foo.geomval).val)) FROM (SELECT p.gid,
>> ST_Intersection(p.cell, r.rast) AS geomval FROM mountain r, priogrid_land
p
>> WHERE ST_Intersects(p.cell, r.rast) AND p.gid =219260) AS foo
>> GROUP BY gid ORDER BY gid;
>> Error:
>> ERROR:  column bmd.hasnodata does not exist
>> LINE 1: SELECT bmd.hasnodataFROM ST_BandMetaData(ras...
>>^
>> QUERY:  SELECT bmd.hasnodataFROM ST_BandMetaData(rast,
>> nband) AS bmd
>> CONTEXT:  PL/pgSQL function "_st_intersects" line 32 at SQL statement
>> PL/pgSQL function "st_intersection" line 5 at assignment
>> ** Error **
>> ERROR: column bmd.hasnodata does not exist
>> SQL state: 42703
>> Context: PL/pgSQL function "_st_intersects" line 32 at SQL statement
>> PL/pgSQL function "st_intersection" line 5 at assignment
>>
>> Thanks for any help.
>> Best regards,
>> Andreas
>

> ___
> 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

[postgis-users] pgsql2shp

2011-12-01 Thread toni hernández 

Hi,

I have some issues with pgsql2shp.

I am using postgis 1.5.3 over postgresql 9.0 on windows.
The database I am working with is utf8.

I have several spatial tables and I want some of them to be exported to 
a shape file.
pgsql2shp seems to be working fine but when I load the generated shape 
files, then  they appear to be empty. No error thrown (on OpenJump).


I have been testing it for a while and I came to realize that pgsql2shp 
does not like tables that only have one field (the spatial one). I can 
not export a table (any) with only one column but if I add a column to 
that very table (type character varying) then the exportation works fine 
and I can load the shp file perfectly


At first I thought it was something about the encoding but now I am lost.

Any ideas how to solve this?
thanks
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Postgis 2.0

2011-12-01 Thread Sandro Santilli 
On Wed, Nov 30, 2011 at 03:06:46PM -0800, Bob Pawley wrote:

> Do you yet have a timeline for the Postgis 2.0 release??

Spring at worst, but it's much more likely that you could
have something to test by the first days of the new year.

A lot depends on how good the postgis users have been
according to Santa :)

--strk; 

  ()   Sign the pledge for PostGIS-2.0 Topology !
  /\   http://www.pledgebank.com/postgistopology
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users