You are invited to Log in or Register a free Frihost Account!

Database for (x,y) coordinates

I'm not a DB genius at all, so I thought maybe someone here is.

I need to create a database model that stores (x,y) coordinates (it sounds like a game). Each x,y coordinate can have many "things" (lets say tID). I need to mainly issue the following queries:

* to get the thing IDs that is situated within a square in the coordinate plane (eg. with 3<x<8 AND 4<y<7);
* to get and set the location of a thing anywhere in the plane.
* to add or delete a thing.

How do you design an efficient database for that? I was thinking of


  x int(11),
  y int(11),
  tid int(11),
  INDEX (x,y)

(sorry if the SQL is wrong in some way) However with this schema, setting and removing thing will be slow because things.x and things.y is indexed... Does anybody have a better way to do it? Or do you think this schema is OK? Is there any non-database solution for this?
Well.. i suggest that you create a table with more focus on the tID rather than the coordinates. Rather than the coordinate containing something, you need to go with something like the things occupying a particular location. This is because you cannot be sure if the coordinates are discrete.. you would like to allow for multiple (x, y) values like (2.334, 1.667) and (2.331, 1.667). Creating an emphasis on the coords will result in a problem where you need to populate the database with too many discrete places.

Once you are done with it, an algorithm to pick out things in a specific portion would be simple, in the very same way you said 3<x<8. The only thing is that, the table needs to be sorted so that this algorithm becomes much more efficient. in case the table isn't sorted, you will need to check each item for coordinates, which would be time consuming and for a greater number of itenaries will increase server load.
Thanks for the reply! I think your points are correct... eventhough I was thinking of having only discrete/integer values for (x,y) coordinates.

This is what you mean right?

  tid int(11),
  x double,
  y double,
  -- plus some other columns related to things (now we do not need another table for them because the primary key is now tid)
  INDEX (x,y)

Just wondering, is this how online RPGs work? Or they use a custom/non-relational database?
online RPGs work on similar models, but the databases they use are highly linked with many relationships between various tables and their values. Most of the time, they don't use packaged database solutions. They use tailor made packages with MySQL, Oracle as the base ware to suit their applications.

Also, RPGs generally don't rely on a human readable query language owing to the huge number of transactions. This is because, a compiled query system can be much more efficient than a human readable system.

What you've done till now seems ok to me Very Happy
Related topics
Fortran Tutorials(77)
science vs. religion
[man]Creación y uso de popups
Dynamic arrangement of buttons in C# Win Form
Could someone help me with this image code
Chaos and Fractals
Hard To Explain.
VBA - Best way to learn?
-> and =>
My JS problem
Need to run something...
a problem for answer
reference to geographical information system
POST a form in the Same page
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

© 2005-2011 Frihost, forums powered by phpBB.