MVSFORUMS.com :: View topic - ST

through

#1: ST_POINT out of bounds. Author: ed.sam13,

Posted: Thu Sep 10, 2015 5:30 pm
—
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?

#2: Author: Nic Clouston, Location: At Home

Posted: Fri Sep 11, 2015 4:22 am
—
Yes - a mainframe! This is a mainframe forum - not PC forum.

#3: Re: ST_POINT out of bounds. Author: kolusu, Location: San Jose

Posted: Fri Sep 11, 2015 9:43 am
—

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.

#4: Author: ed.sam13,

Posted: Fri Sep 11, 2015 11:22 am
—
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

#5: Author: kolusu, Location: San Jose

Posted: Fri Sep 11, 2015 12:02 pm
—
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

#6: Author: kolusu, Location: San Jose

Posted: Fri Sep 11, 2015 12:04 pm
—

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

#7: Author: ed.sam13,

Posted: Fri Sep 11, 2015 6:43 pm
—
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

MVSFORUMS.com -> Database

output generated using printer-friendly topic mod. All times are GMT - 5 Hours

Page 1 of 1