Re: Triggers and Spatial

For about 8 months, i have been using this trigger:

CREATE OR REPLACE FUNCTION "public"."tr_position_geopoint" () RETURNS 
trigger AS'
BEGIN
   IF (NEW.longitude IS NOT NULL OR NEW.longitude = 0) AND (NEW.latitude 
IS NOT NULL OR NEW.latitude = 0) THEN
     BEGIN
       NEW.geopoint=''POINT('' || NEW.longitude || '' '' || NEW.latitude 
|| '')'';
       -- rough a hundred meter (3 sec)
       SELECT INTO NEW.location_road_name name FROM t_location_road 
WHERE the_geom && expand(NEW.geopoint,0.000278) ORDER BY 
distance(the_geom,NEW.geopoint) ASC LIMIT 1 OFFSET 0;
       IF NEW.location_road_name IS null THEN
         -- rough 30 meters (1 sec)
         SELECT INTO NEW.location_road_name name FROM t_location_road 
WHERE the_geom && expand(NEW.geopoint,0.000833) ORDER BY 
distance(the_geom,NEW.geopoint) ASC LIMIT 1 OFFSET 0;
       END IF;
       RETURN NEW;
     END;
   END IF;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

the geopoint is geometry type (POINT), longitude and latitude is double
the t_location_road.the_geom is database of linestrings

Romi H

installation problem

Hi all,

I want to use the rpm package to install postgis in my rh9, and want mapserver can support phpmapscript and
perlmapscript, How to do?


Good Luck
 				
　　　　　　　　　　　　　　Litao Wei
　　　　　　　　　　　　　　wlt008 <at> sina.com
　　　　　　　　　　　　　　　　　2004-03-01

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

Re: installation problem

Hi Litao,

Personally I use Knoppix (not Red Hat) and installed
PostGIS.

For MapServer, I downloaded and tryed the source code:
mapserver-4.0.1.tar.gz from site:

http://mapserver.gis.umn.edu/dload.html 

When I configure mapserver, I did

./configure ... --with-postgis....

in order to phpmapscript, mapserver to connect to
postgis

It works for me.

 --- Litao Wei <wlt008 <at> sina.com> a écrit : > Hi all,
> 
> I want to use the rpm package to install postgis in
> my rh9, and want mapserver can support phpmapscript
> and perlmapscript, How to do?
> 
> 
> Good Luck
>  				
> ¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Litao Wei
> ¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡wlt008 <at> sina.com

Re: installation problem

Install the postgis rpms and compile mapserver from source. The 
mapserver RPM does not include mapscripts.
Paul

On Monday, March 1, 2004, at 05:46 AM, Litao Wei wrote:

> Hi all,
>
> I want to use the rpm package to install postgis in my rh9, and want 
> mapserver can support phpmapscript and perlmapscript, How to do?
>
>
> Good Luck
>  				
> 　　　　　　　　　　　　　　Litao Wei
> 　　　　　　　　　　　　　　wlt008 <at> sina.com
> 　　　　　　　　　　　　　　　　　2004-03-01
> _______________________________________________
> postgis-users mailing list
> postgis-users <at> postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
      Paul Ramsey
      Refractions Research
      Email: pramsey <at> refractions.net
      Phone: (250) 885-0632

[Fwd: [virusalert <at> mail.ci.collierville.tn.us: VIRUS (Worm/Bagle.E) FROM <postgis-users-bounces <at> postgis.refractions.net>]]

To the list administrator,I don't know what the list administrator can
do about this problem. An individual subscriber is unwittingly sending
the worm Bagle.E through the list.  The traceback information supplied
by my SysAdmin is attached.
-- 
James H. Butcher, G.G.(GIA)
GIS Manager
Town of Collierville
Division of Planning
500 Poplar View Pkwy
Collierville, TN 38017
P: 901-457-2360
F: 901-457-2354
email: jbutcher <at> ci.collierville.tn.us

-----BEGIN GEEK CODE BLOCK-----
Version: 3.12
GAT d+ s---:+ a+ C+++ UL++++ P+ L++ E--- W+++ N+ o+ K- w---
O M V PS-- PE++ Y+ PGP++ t++ 5++++ X++ R++ tv++ b++++ DI+++ D++
G e+++ h r y+
------END GEEK CODE BLOCK------

From: Don Petrowski <dpetrowski <at> ci.collierville.tn.us>
Subject: [virusalert <at> mail.ci.collierville.tn.us: VIRUS (Worm/Bagle.E) FROM <postgis-users-bounces <at> postgis.refractions.net>]
(Continue reading)

VIRUS ENCONTRADO: Price

Panda Antivirus ha encontrado los siguientes virus en el mensaje:
	Enviado por: 	postgis-users-bounces <at> postgis.refractions.net
	Dirección:   	postgis-users-bounces <at> postgis.refractions.net
	Para:        	postgis-users <at> postgis.refractions.net
	Asunto:      	[postgis-users] Price
	Fecha:       	01/03/2004  17:49:46

          	Fichero: 		dentro de: acb.zip
		Virus: W32/Bagle.E.worm	 - Desinfectado
http://www.pandasoftware.com

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

LWGEOM -- initial lwgeom.h file

I quickly put together a lwgeom.h file so you could see what the lwgeom 
type will look like and the basic API for it.

If folks could take a look and give me some feedback, I'll incorporate 
your suggestions.

As I note in the header, the "get" functions for the LWGEOM might be 
slow, so we might want to make an "analysed lwgeom" that pre-computes 
the position of the actual sub-geometries.

typedef struct
{
	char    *serialized_form;
	char    **subgeometries;
	int     numSubGeometries;
} LWGEOM_analysed;

and :

lwgeom_getsubgeometry(serialized_form, n) == 
<LWGEOM_analysed>.subgeometries[n]

dave
ps. C really sucks compared to C++ and Java!

//lwgeom.h

// basic API for handling the LWGEOM, BOX2DFLOAT4, LWPOINT, LWLINE, and LWPOLY.

Re: LWGEOM -- initial lwgeom.h file

Hi David,

I think it would be good not to have to do any allocations to used these 
things. Make everything a LW_GEOM then there is no need to allocate, 
copy and reformat.

Lets take the LW_LINE for example, I suggest there is no LW_LINE type

some macros

LW_SRID(lw_geom) //returns the SRID if one exists or -1 if none
LW_TYPE(lw_geom) //returns the type
LW_LINEPOINTS(lw_geom) //gets point data

debug version can do checking on the types etc.

so then we can do

switch(LW_TYPE(lw_geom) ){
    case LW_LINE:
          if ( LW_3D(lw_geom) ){
                POINT3D * points = LW_LINEPOINTS(lw_geom);
                //use points
        }
          else{
                POINT2D * points = LW_LINEPOINTS(lw_geom);
                //use points
            }

       default:

Re: PERFORMANCE PROBLEMS

Hi! Im developing an app which acceses PostGIS with JDBC. I have a table 
with a geometry column, and Ive created an spatial index on this geometry 
column. The main query is like this:
<query>
SELECT  asText(GEOMETRY) AS GEOMETRY,C_PV,C_OCA,D_OCA,GID FROM ocas WHERE 
GEOMETRY && 'BOX3D(88034.16 3926963.056375266, 634143.4400000001 
4339164.943624733'::box3d
</query>

I've noticed that this is a very slow query.
May I force the use of the spatial index? How can I do that?
May I use instead of this query a PostgreSQL cursor?
What are advantages of cursor?
Thanks in advance!!

_________________________________________________________________
STOP MORE SPAM with the new MSN 8 and get 2 months FREE* 
http://join.msn.com/?page=features/junkmail

Re: PERFORMANCE PROBLEMS II (More Data)

My Java application is a JUMP plugin.
Refraction's plugin es good, but it works downloading all features from 
database, to work with them in memory.
We are working with massive datasets, and we need to execute one query by 
rectangle any time.
(When changing zoom level, plugin execute a query by rectangle)

I've made a query explain -to see if the query is using spatial index) and I 
get:
"Index Scan using spt_index_muni on municipios  (cost=0.00..5.95 rows=1 
width=86)"
It uses spatial index.
May I improve the query execution time? How?
My table only has 700 rows.

>From: "Alvaro Zabala" <alvaro_zabala <at> hotmail.com>
>Reply-To: PostGIS Users Discussion <postgis-users <at> postgis.refractions.net>
>To: postgis-users <at> postgis.refractions.net
>Subject: Re: [postgis-users] PERFORMANCE PROBLEMS
>Date: Tue, 02 Mar 2004 13:53:50 +0100
>
>Hi! Im developing an app which acceses PostGIS with JDBC. I have a table 
>with a geometry column, and Ive created an spatial index on this geometry 
>column. The main query is like this:
><query>
>SELECT  asText(GEOMETRY) AS GEOMETRY,C_PV,C_OCA,D_OCA,GID FROM ocas WHERE 
>GEOMETRY && 'BOX3D(88034.16 3926963.056375266, 634143.4400000001 
>4339164.943624733'::box3d
></query>