Mapping coordinates from database using Unfolding

lriq

Hi all, I've been using Unfolding Maps and found it very useful. I was wondering if it was possible to map lat/long coordinates directly from a MySQL database. On the Unfolding maps site, there seems to be no tutorial regarding this, though it says it's possible. I've used the Beziersqlib to connect to the database. Does anyone know how to query and map points directly from the DB?

tnagel

Hi,

in principle you have to connect to the database, query your lat/long coordinates, and map them to screen coordinates. You can either do that manually (via map.getScreenPosition(location)), or by creating Markers.

Here is some untested code (sitting in a train right now). You need to use your actual database credentials, of course.

import de.fhpotsdam.unfolding.*;
import de.fhpotsdam.unfolding.geo.*;
import de.fhpotsdam.unfolding.utils.*;
import de.fhpotsdam.unfolding.marker.*;
import de.bezier.data.sql.*;

String dbName = "myDatabase";
String dbUser = "myUsername";
String dbPass = "myPassword";
MySQL database;

UnfoldingMap map;

void setup() {
  size(800, 600);

  map = new UnfoldingMap(this);
  MapUtils.createDefaultEventDispatcher(this, map);

  database = new MySQL(this, "localhost", dbName, dbUser, dbPass);
  if (database.connect()) {
    database.query( "SELECT * FROM my_table_with_locations" );
    while (database.next ()) {
      String name = dbconnection.getString("name");
      float lat = dbconnection.getFloat("lat");
      float lng = dbconnection.getFloat("lng");
      Location location = new Location(lat, lng);
      println(name + ": " + location);

      // Store data in own object, see Unfolding example BikeObjectsMap

      // Or: Create Unfolding Marker here, e.g.
      // map.addMarker(new SimplePointMarker(location);
    }
  } else {
    println("Could not connect to database. Check properties.");
  }
}


void draw() {
  map.draw();
}

lriq

Thanks for the advice! It's working

lriq

I've stored the coordinates in 2 arraylists (2 map areas), and I've managed to display them onscreen. I have 2 side-by-side maps. The problem is that points from one map are displayed outside of the map area, overlapping onto the other map. Is there a way I can restrict points to within a map area, perhaps by limiting draw() in a specific area?

PhiLho

Check the coordinate values against the bounds of the map. (Using < and >, of course.)

lriq

It took me a while, but I worked it out. Thanks for the help!

Here's the code (may be useful for someone else):

import de.fhpotsdam.unfolding.*;
import de.fhpotsdam.unfolding.geo.*;
import de.fhpotsdam.unfolding.utils.*;
import de.fhpotsdam.unfolding.providers.*;
import de.fhpotsdam.unfolding.data.*;``
import de.fhpotsdam.unfolding.mapdisplay.MapDisplayFactory;

import de.bezier.data.sql.*;

UnfoldingMap map1;
UnfoldingMap map2;
MySQL msql;

ArrayList<Events> aevents = new ArrayList();    //2 arraylists for points in 2 areas
ArrayList<Events> bevents = new ArrayList();

public void setup() {
    size(1300, 650, P2D);
    smooth();

map1X = 250;
map1Y = 10;
map2X = 775;
map2Y = 10;
mapW = 515;
mapH = 630;

map1 = new UnfoldingMap(this, "map1",  map1X, map1Y, mapW, mapH, true, false, new Google.GoogleTerrainProvider());
map2 = new UnfoldingMap(this, "map2", map2X, map2Y, mapW, mapH, true, false, new Google.GoogleTerrainProvider());
MapUtils.createDefaultEventDispatcher(this, map1, map2);

map1.zoomAndPanTo(new Location(-37.7f, 144.8f), 11);
map2.zoomAndPanTo(new Location(-37.8f, 145.1f), 11);
float maxPanningDistance = 30;   // in km
map1.setPanningRestriction(new Location(-37.7f, 144.8f), maxPanningDistance);
map2.setPanningRestriction(new Location(-37.8f, 145.1f), maxPanningDistance);

    //connect to MySQL database
    String user = "****";
    String pass = "****";
    String database = "database";

    msql = new MySQL(this, "localhost", database, user, pass);

    if (msql.connect())
    { //Events a
      msql.query("select ... etc;");
      while (msql.next ())
      {
        String l = msql.getString("locationType");
        int id = msql.getInt("participant_id");
        float lat = msql.getFloat("lat"); 
        float lon = msql.getFloat("lon");
        println("A " + l + "   " + id + "   " + lat + "   " + lon);

        //create new empty object to store data
        Events aevent = new Events();
        // Add to list of all events
        aevents.add(aevent); 
        //location of events
        Location location = new Location(lat, lon);
        }
    } 

    if (msql.connect())     
    { //Events b
      msql.query("select ... etc;");
      while (msql.next ())
      {   
        String l = msql.getString("locationType");
        int id = msql.getInt("participant_id");
        float lat = msql.getFloat("lat"); 
        float lon = msql.getFloat("lon");
        println("B " + l + "   " + id + "   " + lat + "   " + lon);

        //create new empty object to store data
        Events bevent = new Events();
        // Add to list of all events
        bevents.add(bevent);
        //location of events
        Location location = new Location(lat, lon);

      }
      println("Number of events for A: " + aevents.size());
      println("Number of events for B: " + bevents.size());
    } else {
      //connection failed
    }
  }

  public void draw() {
    background(230);
    map1.draw();
    map2.draw();
    //Darken map slightly
    fill(0, 100);
    rect(0, 0, width, height);

    noStroke();

    // Iterate over all A events
    for (Events aevent : aevents) {
      // Convert geo locations to screen positions
      ScreenPosition pos = map1.getScreenPosition(aevent.location);
    //Check if points within map boundaries. If so, display.
if( pos.x > map2X  && pos.x < map2X + mapW &&
     pos.y > map2Y  && pos.y < map2Y + mapH  ){ 
      // Draw circle for events
      fill(255, 0, 255, 50);
      ellipse(pos.x, pos.y, 8, 8);
}
    }
    // Iterate over all B events
    for (Events bevent : bevents) {
      // Convert geo locations to screen positions
      ScreenPosition pos = map2.getScreenPosition(bevent.location);
        //Check if points within map boundaries. If so, display.
if( pos.x > map1X && pos.x < map1X + mapW &&
         pos.y > map1Y && pos.y < map1Y + mapH  ){

      // Draw circle for events
      fill(50, 255, 100, 50);
      ellipse(pos.x, pos.y, 8, 8);
    }
}
  }

  class Events {

    int participant_id;
    String locationType;
    double lat;
    double lon;
    Location location;

  }

tnagel

For future reference: If you are using Unfolding's Marker they would appear only on the map itself (as it cuts them off at the border). No need for checks, then.

lriq

(related question posted as new discussion)