Mixing geometry types, is there a cost when I come to querying it later?

0
votes

I have a mysql table with a field of the type POLYGON, containing a number of polygons, however I have discovered I also need to store POINTS.

I'd like to store the geometry types POLYGONS and POINTS in the same field.

The manual page suggests this is possible, by using the data type GEOMETRY.

Are there any drawbacks to using the more generic geometry type GEOMETRY to do this? Will I be losing access to any extra functionality by mixing POLYGONS and POINTS when I come to query this data at a later stage?

EDITED

To clarify my situation:

I have a table call it 'locations' each location will either best be described as a single POINT or an area a POLYGON but they will always have either one or the other.

So should the structure resemble:

locations:
id int
title var
single_point (POINT)
area (POLYGON)

or

locations:
id int
title var
coords (GEOMETRY)

or, as suggested in the comments below a 3 table solution?

The points and polygons will be baked into kml files in the first instance for display in web maps.

I envisage that the geometry fields will be indexed in order to make the best use of other spatial type queries in the future (nearest, largest area and so on).

1
mysqlgeometrygeospatial
Why not use a separate column (or even a separate table depending on the relationships involved)? Do the POINTS and POLYGONS really represent the same thing? – Chris Morgan
Yes, that is one alternative. As I understand it, spatial fields can not be NULL so I'd end up doing a horrible string test to work out whether a particular place was best described as an area or a spot. I figured it would be just as easy to recognise POINT() and POLYGON() an act accordingly. Hence my Q really... seeing as I'm a noob to this type of spatial data. – Cups
@Cups spatial fields can be null if they aren't indexed. If you're doing spatial queries that would benefit from indexing, which you probably are, then the 2-table (really 3-table: base_table, point_table, polygon_table) solution is probably better than the two-column solution. – Chris Morgan
@Chris Morgan thanks for the advice, but why is a dedicated point_table a better solution? Each 'Location' (base_table) will have either a point or a polygon. What is the main benefit, or if you can point me to somewhere where I can read more on this. Cheers for your replies btw. – Cups
I edited my original question in an attempt to clarify the situation. – Cups

1 Answers

0
votes

The way I have traditionally done this is to have 3 tables:

locations:
id int
title varchar(100)

location_points:
id int (FK to locations)
point_coords POINT

location_area:
id int (FK to locations)
poly_coords POLYGON

You could then create a view that would mimic your two-column table design:

SELECT loc.id AS id, loc.title AS title, 
       p.point_coords as single_point, a.poly_coords as area_poly
FROM locations loc
LEFT OUTER JOIN location_points p ON loc.id = p.id
LEFT OUTER JOIN location_area a on loc.id = a.id
WHERE a.id IS NOT NULL OR b.id IS NOT NULL;