MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

ST_POINT out of bounds.

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
ed.sam13
Beginner


Joined: 09 Aug 2010
Posts: 31
Topics: 11

PostPosted: Thu Sep 10, 2015 5:30 pm    Post subject: ST_POINT out of bounds. Reply with quote

When trying to geocode a particular address we got the following geocodes - (37.09024, -95.712891) from Google geocoding API.
But when i use function ST_POINT to arrive at a point, it is giving me SQLCODE -443. When I change the longitude to < -90 degrees then it is good.
Is it an issue with this function? Because this seems to be a valid geocode and I am able to plot this on the map.

Code:
,,SELECT                                                                00
  DB2GSE.ST_POINT('POINT(37.09024  -95.712891)',1)                      00
    FROM SYSIBM.SYSDUMMY1 A                                             00
    WITH UR                                                             00
  ;                                                                     00
DSNT408I SQLCODE = -443, ERROR:  ROUTINE GSEGEOMFROMTEXT (SPECIFIC NAME
         STCO00002GFT) HAS RETURNED AN ERROR SQLSTATE WITH DIAGNOSTIC TEXT
         GSE3416N  Coordinate out of bounds.
DSNT418I SQLSTATE   = 38SSG SQLSTATE RETURN CODE
DSNT415I SQLERRP    = DSNXRUFS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD    = -101 0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD    = X'FFFFFF9B'  X'00000000'  X'00000000'  X'FFFFFFFF'
         X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION


Also I have DB2 Express-C version 10.5 installed on my computer. I am not able to create spatial columns in it. The documentation says I should be able to do it. Is there something I may be missing?
Back to top
View user's profile Send private message
Nic Clouston
Advanced


Joined: 01 Feb 2007
Posts: 1075
Topics: 7
Location: At Home

PostPosted: Fri Sep 11, 2015 4:22 am    Post subject: Reply with quote

Yes - a mainframe! This is a mainframe forum - not PC forum.
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Fri Sep 11, 2015 9:43 am    Post subject: Re: ST_POINT out of bounds. Reply with quote

ed.sam13 wrote:
Also I have DB2 Express-C version 10.5 installed on my computer. I am not able to create spatial columns in it. The documentation says I should be able to do it. Is there something I may be missing?


ed.sam13,

check this link for Verifying the installation of spatial support

And also Getting started with IBM Spatial Support for DB2 for z/OS


Nic,

The question is very much mainframe related and z/OS DB2 11.0 does support Spatial Support.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
ed.sam13
Beginner


Joined: 09 Aug 2010
Posts: 31
Topics: 11

PostPosted: Fri Sep 11, 2015 11:22 am    Post subject: Reply with quote

hi Kolusu,
Thanks for the information. I should have clarified more. There were 2 questions in the post actually.
1) We already have spatial extender and other spatial packages installed on z/OS. The error that I showed above was while executing ST_POINT function from Mainframe.
The geocode (37.09024 -95.712891) is very valid on google maps.

But when I use ST_POINT i am getting the above mentioned error for these co-ordinates.

When the longitude is < -90 for example - if I change -95.712891 to -85.712891, i am getting a valid ST_POINT.
Code:

  SELECT
  DB2GSE.ST_POINT('POINT(37.09024  -85.712891)',1) AS ST_POINT_VALUE
    FROM SYSIBM.SYSDUMMY1 A
    WITH UR
  ;
---------+---------+---------+---------+---------+---------+---------+--
ST_POINT_VALUE
---------+---------+---------+---------+---------+---------+---------+--
C7E2C5C8022004E0000000010000000140428B8CFBFC6540C0556DA00192A73701010000
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL

But Longitude values are valid from 0 to +/-180 degrees. Why doesnt DB2 accept -95.712891?

2) On DB2 EXPRESS-C which I installed locally on my computer, when I try to create a table with spatial columns, it says those column definitions are not recogonized. Is there something that I need to install extra for the spatial extender for Express -C luw ?

Thanks,
Ed
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Fri Sep 11, 2015 12:02 pm    Post subject: Reply with quote

ed.sam13,

Try setting up the Location like this

Code:

set location = db2gse.st_point(37.09024 -95.712891,1);


and then issue the ST_POINT command
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Fri Sep 11, 2015 12:04 pm    Post subject: Reply with quote

ed.sam13 wrote:
2) On DB2 EXPRESS-C which I installed locally on my computer, when I try to create a table with spatial columns, it says those column definitions are not recogonized. Is there something that I need to install extra for the spatial extender for Express -C luw ?

Thanks,
Ed


I suggest you go thru Manage spatial data with IBM DB2 Spatial Extender, Part 1: Acquiring spatial data and developing applications
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
ed.sam13
Beginner


Joined: 09 Aug 2010
Posts: 31
Topics: 11

PostPosted: Fri Sep 11, 2015 6:43 pm    Post subject: Reply with quote

hi Kolusu, Thanks it worked.

Also the POINT function was expecting longitude, latitude. So when I gave the following, it worked.
Code:

  SELECT
  DB2GSE.ST_POINT('POINT(-95.712891 37.09024)',1)
    FROM SYSIBM.SYSDUMMY1 A
    WITH UR
  ;


Thanks for your help
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group