1

I am getting a strange error within GIS, when using the internal MapInfo Select query for objects.

SELECT featurecode, area(obj,"sq km"),theme, DescriptiveGroup,count(*) from mastermap_topo_497145_0_Topogr GROUP BY featurecode

Shows up all the results, with the area in KM, now when its applied to an object area, for example to select all objects within a distance of a point, even a large value it does not give the same results.

The counts are equal (only one layer) per featurecode, but the area calculations are wrong and vastly different as if the SQL command is not selecting all the objects but the object count is equal,

BTW the point creation / sql is via MapInfo Basic.

Just very odd to get the same objects two different results.

Improve this question

1 Answer 1

Reset to default
2

When doing a group-by you need to do it this way:

select field1, field2, aggregate(field3), aggregate(field4), count(*)
from ..
group by field1, field2

This means fields NOT listed in the group by, needs to be aggregated in some way such as sum, avg etc. Alternatively they should not be in the select at all.

In your case you have both area(obj,"sq km"), theme and DescriptiveGroup breaking that rule.

You should either:

  1. Remove them,
  2. Add them to the group by or
  3. Add an aggregating function in the select part
Improve this answer
4
  • ok, if I use "featurecode, Area(obj, "sq km"), count(*)" it shows the results, order by featurecode, but if i add sum() for the Area then it goes wrong and shows different results
    – Paul
    Commented Mar 26, 2014 at 10:34
  • This fault only shows for OS Mastermaps, because if I use a global select (no where) it shows count, sum of area, but when i add to select objects within buffer it causes the fault BUT the same layer, same object count, so i am sure they are the same objects :( just different results
    – Paul
    Commented Mar 26, 2014 at 10:47
  • Sum() should be the correct result. Without sum I think you get the area of the first object for that group - but it is undefined anyway, so stay away from it
    – Uffe Kousgaard
    Commented Mar 26, 2014 at 10:47
  • Uffe is right. If you don't use an aggrerate on the Area() function you will get the area of a not specified object in the result set, probably the first, but this is not garanteed. Also not that if you add a Where condition to the query the result set will be different and the area will probably be calculated on a different object than in the query without the where condition
    – Peter Horsbøll Møller
    Commented Mar 31, 2014 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.