Re: [mapserver-users] Oracle SYSDATE in EXPRESSION

[Basques, Bob (CI-StPaul)]

Thanks Martin,

I'll take a look at it again.  I thought I had tried all possible combinations 
of brackets/quoting, etc. though  . . .

bobb



> On Apr 5, 2016, at 1:36 AM, Martin Icking  wrote:
> 
> I tried this DATA statement successfully with Oracle:
> 
> DATA "GEOLOC FROM (SELECT GEOLOC,OBJECTID,START_DATE,
> (SYSDATE-START_DATE)/365 as age  FROM SPATIAL_TABLE) USING UNIQUE OBJECTID
> SRID 27700" 
> 
> [AGE] can then be used in any styling.
> 
> Martin
> 
> 
> 
> --
> View this message in context: 
> http://osgeo-org.1560.x6.nabble.com/Oracle-SYSDATE-in-EXPRESSION-tp5258709p5259649.html
> Sent from the Mapserver - User mailing list archive at Nabble.com.
> ___
> mapserver-users mailing list
> mapserver-users@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users

___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [mapserver-users] Oracle SYSDATE in EXPRESSION

[Martin Icking]

I tried this DATA statement successfully with Oracle:

DATA "GEOLOC FROM (SELECT GEOLOC,OBJECTID,START_DATE,
(SYSDATE-START_DATE)/365 as age  FROM SPATIAL_TABLE) USING UNIQUE OBJECTID
SRID 27700" 

[AGE] can then be used in any styling.

Martin



--
View this message in context: 
http://osgeo-org.1560.x6.nabble.com/Oracle-SYSDATE-in-EXPRESSION-tp5258709p5259649.html
Sent from the Mapserver - User mailing list archive at Nabble.com.
___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [mapserver-users] Oracle SYSDATE in EXPRESSION

[Basques, Bob (CI-StPaul)]

I went the view route for the time being, to get one of the class types (layer) 
up and running.  I was basing my questions on the fact that I’ve successfully 
passed in all sorts of SQL into the Postgres connector with MapServer, but the 
Oracle connector seems to be some limited in what it can send in successfully, 
or there are some escaping sequences I don’t know about.

I tried a few things for making it work the fly, but nothing was working.

I ended up creating a view as Dan Little suggested.  I could probably get by 
with doing the FOLDERTYPE filtering (Last line below) in the MapFile as a quick 
way of making all the Permit class types work.

create view
  PW_SK_Permits
as
select
  P.DEPARTMENT,
  P.INDATE,
  CASE WHEN
P.INDATE < SYSDATE - 365 * 5
THEN
 'older'
ELSE
  'newer'
  END as age,
  P.FOLDERTYPE,
  P.FOLDERDESC,
  P.FOLDERRSN,
  P.FOLDERID,
  A.PIN,
  A.GEOMETRY,
  A.OGR_FID
from
  STAMP.PW_Permits P, ADDRESS_ACTIVE_OGR_VIEW A
where
  P.PIN is not NULL
and
  P.PIN = A.PIN
and
  P.FOLDERTYPE IN ('SK')  -- PW Sidewalks

I needed to get this SideWalk Permit layer up and running either way.  
Something similar to the inner select above works just fine via 
MapServer/Postgres though.

Now how to figure things out for the other 119 permit types.  :c)

bobb


On Apr 4, 2016, at 5:28 AM, Martin Icking 
> wrote:

Or you could create a calculated column in the data statement, call it e.g.
"AGE" being the result of the difference of your original date column and
the oracle sysdate.
Then you can easily use that "AGE" column to do your styling.

HTH
Martin



--
View this message in context: 
http://osgeo-org.1560.x6.nabble.com/Oracle-SYSDATE-in-EXPRESSION-tp5258709p5259480.html
Sent from the Mapserver - User mailing list archive at 
Nabble.com.
___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [mapserver-users] Oracle SYSDATE in EXPRESSION

[Martin Icking]

Or you could create a calculated column in the data statement, call it e.g.
"AGE" being the result of the difference of your original date column and
the oracle sysdate.
Then you can easily use that "AGE" column to do your styling.

HTH
Martin



--
View this message in context: 
http://osgeo-org.1560.x6.nabble.com/Oracle-SYSDATE-in-EXPRESSION-tp5258709p5259480.html
Sent from the Mapserver - User mailing list archive at Nabble.com.
___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [mapserver-users] Oracle SYSDATE in EXPRESSION

[Dan Little]

Again, you could do that with the stored procedure or in SQL with a view.
And have a column that returns the logical style class. For example,

CREATE VIEW
SELECT
  CASE WHEN permit_date < SYSDATE - 365 * 5 THEN 'old_permit'
   ELSE 'active_permit' END as permit_class,
   ... other columns ...
FROM permits;


Then in the mapbook...

CLASS
 EXPRESSION "[permit_class] == 'old_permit'"
 STYLE
   [ style stuff ]
 END
END

On Tue, Mar 29, 2016 at 11:20 AM, Basques, Bob (CI-StPaul) <
bob.basq...@ci.stpaul.mn.us> wrote:

> Dan,
>
> That would work, but I was trying to color a 5 year old class one color,
> and the rest of them another, and do it all in one SQL call (single layer)
>
> I got it to work in the DATA connection, but was trying to do the STYLE
> based query in the CLASS block.
>
> Seems like it would be doable, but I’m not having any luck yet.  I’m
> guessing that the SYSDATE is not getting passed in correctly along with the
> INDATE value, at least based on what I see when I intentionally break the
> SQL to see the error.
>
> Also, there are only 1700 records to work with in there right now, quick
> return no matter what.
>
> bobb
>
>
> On Mar 29, 2016, at 11:10 AM, Dan Little  wrote:
>
> You're probably better off with a view or querying against a stored
> procedure. There are a few advantages:
> 1. With a view you can create multiple arbitrary views.
> 2. It keeps the syntax of the mapfile cleaner.
> 3. A view is easy to change to a materialized view in Oracle.  That has a
> pile of performance benefits.
> 4. A stored procedure can be used to normalize the data better between
> Oracle and mapserver.
>
>
> On Tue, Mar 29, 2016 at 10:53 AM, Basques, Bob (CI-StPaul) <
> bob.basq...@ci.stpaul.mn.us> wrote:
>
>> All Oracle Guru’s,
>>
>> How do I handle an expression for doing a timed query in Oracle:
>>
>> CLASS
>> *EXPRESSION ('[indate] > SYSDATE - 1825')*
>> COLOR 255 255 254
>> OUTLINECOLOR 0 0 0
>>   SIZE 10
>>   SYMBOL 'circle'
>> NAME "PW Sidewalk Permits"
>> END
>>
>> I want to get all the Permits up to 5 years old to plot.  What should the
>> EXPRESSION line look like above.  I’ve tried all sort of different
>> combinations of quoting, etc.  I either get everything, or nothing.
>>
>> Thanks
>>
>> bobb
>>
>>
>>
>> ___
>> mapserver-users mailing list
>> mapserver-users@lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>>
>
>
>
___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [mapserver-users] Oracle SYSDATE in EXPRESSION

[Basques, Bob (CI-StPaul)]

Dan,

That would work, but I was trying to color a 5 year old class one color, and 
the rest of them another, and do it all in one SQL call (single layer)

I got it to work in the DATA connection, but was trying to do the STYLE based 
query in the CLASS block.

Seems like it would be doable, but I’m not having any luck yet.  I’m guessing 
that the SYSDATE is not getting passed in correctly along with the INDATE 
value, at least based on what I see when I intentionally break the SQL to see 
the error.

Also, there are only 1700 records to work with in there right now, quick return 
no matter what.

bobb


On Mar 29, 2016, at 11:10 AM, Dan Little 
> wrote:

You're probably better off with a view or querying against a stored procedure. 
There are a few advantages:
1. With a view you can create multiple arbitrary views.
2. It keeps the syntax of the mapfile cleaner.
3. A view is easy to change to a materialized view in Oracle.  That has a pile 
of performance benefits.
4. A stored procedure can be used to normalize the data better between Oracle 
and mapserver.


On Tue, Mar 29, 2016 at 10:53 AM, Basques, Bob (CI-StPaul) 
> wrote:
All Oracle Guru’s,

How do I handle an expression for doing a timed query in Oracle:

CLASS
EXPRESSION ('[indate] > SYSDATE - 1825')
COLOR 255 255 254
OUTLINECOLOR 0 0 0
  SIZE 10
  SYMBOL 'circle'
NAME "PW Sidewalk Permits"
END

I want to get all the Permits up to 5 years old to plot.  What should the 
EXPRESSION line look like above.  I’ve tried all sort of different combinations 
of quoting, etc.  I either get everything, or nothing.

Thanks

bobb



___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users


___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [mapserver-users] Oracle SYSDATE in EXPRESSION

[Dan Little]

You're probably better off with a view or querying against a stored
procedure. There are a few advantages:
1. With a view you can create multiple arbitrary views.
2. It keeps the syntax of the mapfile cleaner.
3. A view is easy to change to a materialized view in Oracle.  That has a
pile of performance benefits.
4. A stored procedure can be used to normalize the data better between
Oracle and mapserver.


On Tue, Mar 29, 2016 at 10:53 AM, Basques, Bob (CI-StPaul) <
bob.basq...@ci.stpaul.mn.us> wrote:

> All Oracle Guru’s,
>
> How do I handle an expression for doing a timed query in Oracle:
>
> CLASS
> *EXPRESSION ('[indate] > SYSDATE - 1825')*
> COLOR 255 255 254
> OUTLINECOLOR 0 0 0
>   SIZE 10
>   SYMBOL 'circle'
> NAME "PW Sidewalk Permits"
> END
>
> I want to get all the Permits up to 5 years old to plot.  What should the
> EXPRESSION line look like above.  I’ve tried all sort of different
> combinations of quoting, etc.  I either get everything, or nothing.
>
> Thanks
>
> bobb
>
>
>
> ___
> mapserver-users mailing list
> mapserver-users@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>
___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users