Subscribe to this thread
Home - General / All posts - Faster way to find the nearest point
bobo_chacha200025 post(s)
#19-Jul-19 07:06

I am expecting to have the nearest distance from the account location to its nearest geocode location with the following query.

SELECT [MRD FINAL].[ACCT_NO],

MIN(Distance([GEO_SPATIAL_LOCATION_APDU 2].[ID], [MRD FINAL].[ID], "m")), [GEO_SPATIAL_LOCATION_APDU 2].[Geocode]

FROM [MRD FINAL], [GEO_SPATIAL_LOCATION_APDU 2]

GROUP BY [MRD FINAL].[ACCT_NO], [MRD FINAL].[POLE_NO], [GEO_SPATIAL_LOCATION_APDU 2].[Geocode]

The issue is, The Table of MRD FINAL have 400,000 + records and GEO_SPATIAL_LOCATION_APDU 2 have 160,000 + records. It took very long time until my computer unit crashes down.

Is there a faster way to do this using SQL having same result.

P.S. I am using M8 in this transaction.

And hoping to hear something from you.

tjhb

8,958 post(s)
#19-Jul-19 08:59

You could use a conditional join, using a realistic nominal radius, rather than a cross join.

bobo_chacha200025 post(s)
#19-Jul-19 10:03

Thank you for your prompt response.

I change my statement as follows;

SELECT [MRD FINAL].[ACCT_NO],

MIN(Distance([GEO_SPATIAL_LOCATION_APDU 2].[ID], [MRD FINAL].[ID], "m")), [GEO_SPATIAL_LOCATION_APDU 2].[Geocode]

FROM [MRD FINAL] INNER JOIN [GEO_SPATIAL_LOCATION_APDU 2]

ON Distance([GEO_SPATIAL_LOCATION_APDU 2].[ID], [MRD FINAL].[ID], "m") < 50

GROUP BY [MRD FINAL].[ACCT_NO], [MRD FINAL].[POLE_NO], [GEO_SPATIAL_LOCATION_APDU 2].[Geocode

Is this what you mean conditional join? Assuming that I want to get the nearest point within 50 m radius.

Though, still my query is running as of this moment.

tjhb

8,958 post(s)
#19-Jul-19 10:22

That is exactly what I meant, yes. Precisely.

It might not be efficient enough... let's see.

Dimitri is right of course that 9 is a better solution. We can help with the new syntax for that.

bobo_chacha200025 post(s)
#23-Jul-19 03:46

tjhb,

The query above extracted no data.

Any suggestion?

tjhb

8,958 post(s)
#23-Jul-19 05:12

Without data, it is hard to say.

Can you supply data? It can be obfuscated if that's necessary, but it should at least have the actual projection(s) you are using.

A guess: perhaps some of your data is in Latitude / Longitude (geographic), so that the Distance function is not applicable. You may need to reproject, or to use DistanceEarth instead (in two places).

(There is a missing "]" at the end of the query text above, but since you said earlier that the code ran, I think that is likely to be just a typo.)

It would be great, too, if you could use the forum Code button.

Attachments:
cmd-insertcode.gif

bobo_chacha200025 post(s)
#23-Jul-19 06:42

You're right, I was actually using lat/long coordinates. Following your recommendation I was able to successfully run and have the nearest point as it should be. Also, the speed of extraction was superb.

Thank you very much. We really appreciate your insights and support.

More to power!

Dimitri


5,620 post(s)
#19-Jul-19 09:57

Is there a faster way to do this using SQL having same result.

Use 9. It's faster than 8 with bigger data. Try it in the free Viewer.

bobo_chacha200025 post(s)
#19-Jul-19 10:08

Thank you for this.

And I tried simply opening this to M9 and let my query run.

Result, cannot parse query.

I really want to explore M9 but overwhelm by different projects as of this moment. Still looking forward to find time in learning the basics in M9.

Manifold User Community Use Agreement Copyright (C) 2007-2019 Manifold Software Limited. All rights reserved.