Lat / Long

[rj5316]

Hi ,

when we create locations and specify the lat long of the locationm, in which table does this get stored.

Also what is "other lat/long" in geography within the power data of transport planning and execution AND what is its use? It looks as though these are cities created in the public domain. But if so then why does it allow me to create a same city with diff lat / long ... and any ways the city is not a manadtory feild when creating locations...

rgds--

[james_foran]

Re: Lat / Long

LAT and LON fields are in the GLOGOWNER.LOCATION table...

I am not sure what Other Lat/Lon is for though. Looks like a set of defaults or something??

[otmrookie]

Re: Lat / Long

I think it is the GEO_POSTAL_POINT table, hope that helps.

[oliver]

Re: Lat / Long

lat/long gets stored in the location-table, in fields "lat" and "lon" (Number 10,5).
On the "same city with diff lat/long", usually cities have more then one lat/long, take "Chennai" as an example.
The city isnt mandatory (neither is lat/long), because OTM wont necessarily need it to calculate transit times. But if you want OTM to calculate the distance between 2 Loc's, it needs lat/long to do so; in the background it uses a formula to include the curve of the surface and all these things to calculate the most realistic distance between to pairs of lat/long.


Rgds
Oliver

[rj5316]

Re: Lat / Long

Thanks OM,

Understand that the other lat /long are cities created in the public domain and cities have diff lat / longs...but when planning does OTM pick up the lat long from the location table ( i.e as recorded by the user) or from the other lat long (the city one)......

alternatively what is the purpose of the other lat / long....

thanks...

[EmergeNow]

Re: Lat / Long

If you create a new location with lat/long, it gets stored in the location table. If you populate the other lat/long tables (GEO_POSTAL_POINT, GEO_CITYPROV_POINT), the location refers to them to look up the lat/long and populate the location table.

For example, I have worked with clients that have purchased the lat/long for every US zip code and I loaded that information in the GEO_POSTAL_POINT table. When a new location was entered with a valid zip code and saved, the lat/long is defined for the location.

[njoyotm]

Re: Lat / Long

Hi,

Can anyone help me out in finding wat formula does OTM use for calculating distance using LAT / LON?
I find that the Great Circle Distance Formula is not bringing out the same results as OTM does.

Regards,
njoyotm

[Stefano]

Re: Lat / Long

Hi
looking in Database PL/SQL PACKAGES you can find in schema GLOGOWNER
Package GPOINT
inside it the function:
"function estimate_distance(fromLat number, fromLon number, toLat number, toLon number) return number"

this function referring to java code in db (DbGPoint.calculateEstimateDistance), you could open (with TOAD or something similar) and check also the java code .

I haven't test if this function return same values returned by UI, but I imagine yes.

Regards

[njoyotm]

Re: Lat / Long

Hi

Thank you very much for the useful information.
I tested the function estimate_distance but there are variations
in the value returned by this function and the value returned via UI.
However many thanks,I am looking into it.
Any suggestions are always welcome.

Regards,
njoyotm

[bman]

Re: Lat / Long

Hi

Thank you very much for the useful information.
I tested the function estimate_distance but there are variations
in the value returned by this function and the value returned via UI.
However many thanks,I am looking into it.
Any suggestions are always welcome.

Regards,
njoyotm

Hi,

Any luck figuring out why the variations with estimate_distance vs. distance/inquiry UI in OTM..?

I'm trying to find a function in OTM tha we can call inside a stored procedure to return the distance between two locations using our distance engine pcmiler city to city.

Do you know if estimate_distance is calling the 3rd party rating engine, pcmiler, we have installed with OTM...?

If not, does anyone know how to call pcmiler from within a stored procedure..?

thanks in advance...Brian

[chrisplough]

Re: Lat / Long

Brian,

I can tell you that the estimate distance is not calling out to a 3rd party distance engine, however I'm not sure that there's a way to call PCMiler from within a stored procedure. To my knowledge this is all within the java code of OTM, with no externally accessible API.

--Chris

[bman]

Re: Lat / Long

Chris,

Thanks for that info. I have emailed Sam with this information as well to see if there is a way to get OTM to produce these results when rating. We were trying to use the BASIS Stop Source Distance (distance to the source stop) but this is apparently still using drive miles vs. straight point-to-point distance calculations as if the shipment was multiple direct shipments to each stop destination.

This is how all of our auto carriers structure their contracts as it puts the risk on them to reduce out of route miles and from my understanding, it is the industry standard, at least for all of our automotive customers.

thanks in advance for any additional info you can provide....Brian

[chrisplough]

Re: Lat / Long

Brian,

No problem. We're now getting into more of the functional side - where my knowledge isn't as strong -- so I'll have to defer to Sam anyways.

I understand how the system calls are working in the background, but not quite how to achieve what you're looking for.

If you need me to prod Sam - just let me know

--Chris

[acuartero]

Re: Lat / Long

Brian,

The connection to the PC*Miler server is initiated from the OTM Application server. As far as I know there is no existing SP designed to access it from within PL/SQL from the database server. However it may be possible to roll your own using the Oracle UTL_TCP package to open a connection to the PC*Miler connect service on a specific port (typically port 8145).

Please see
UTL_TCP , Oracle 10gR2
UTL_TCP, Oracle 9i

The request to the PC*Miler server is a simple text query and is documented in the PC*Miler Connect API manual.

http://www.alk.com/support/downloads/pcmiler/manuals/ww_connect_18_forpdf.pdf

Here is a simple example using telnet to access the server and passing city names or zip codes.

$telnet pcmiler.mydomain.com 8145
Connected to pcmiler.mydomain.com (192.168.1.200).
Escape character is '^]'.
ALK PCMILER SERVER READY
pcmscalcdistance("New York, NY","Boston, MA",1)
2156
READY
pcmscalcdistance(10580,06811)
435

The distance is returned in tenths of a mile.

I hope this helps.

-Alan

[[email protected]]

Re: Lat / Long

Can anyone share the exact syntax for testing the statement below that was shared earlier in this thread? I have tried to use with some sample lat/lon data in TOAD but get errors

"function estimate_distance(fromLat number, fromLon number, toLat number, toLon number) return number"