Finding near locations with GeoDjango and Postgis Part I

With GeoDjango we can find places in proximity to other places – this is very useful for things like a store locator. Let’s use a store locater as an example. Our store locator needs to be able to read in messy user input (zip, address, city, some combination). Then, locate any stores we have nearby.

General concept and theory

Screenshot from 2015-10-01 17-16-31

We have two problems to solve. One is to turn messy address input into a point on the globe. Then we need a way to query this point against other known points and determine which locations are close.

Set up known locations

Before we can really begin we need to set up GeoDjango. You can read the docs or use docker-compose.

It’s still a good idea to read the tutorial even if you use docker.

Let’s add a location. Something like:

class Location(models.Model):
    name = models.CharField(max_length=70)
    point = models.PointField()

    objects = models.GeoManager()

A PointField stores a point on the map. Because Earth is not flat we can’t use simple X, Y coordinates. Luckily you can almost think of Latitude and Longitude as X, Y. GeoDjango defaults to this. It’s also easy to get Latitude and Longitude from places like Google Maps. So if we want – we can ignore the complexities of mapping coordinates on Earth. Or you can read up on SRID if you want to learn more.

At this point we can start creating locations with points – but for ease of use add GeoModelAdmin to Django Admin to use Open Street Maps to set points.

from django.contrib import admin
from django.contrib.gis.admin import GeoModelAdmin
from .models import Location

@admin.register(Location)
class LocationAdmin(GeoModelAdmin):
    pass

Screenshot from 2015-10-01 17-31-54

Wow! We’re doing GIS!

Add a few locations. If you want to get their coordinates just type location.point.x (or y).

Querying for distance.

Django has some docs for this. Basically make a new point. Then query distance. Like this:

from django.contrib.gis.geos import fromstr
from django.contrib.gis.measure import D
from .models import Location

geom = fromstr('POINT(-73 40)')
Location.objects.filter(point__distance_lte=(geom, D(m=10000)))

m is meters – you can pass all sorts of things though. The result should be a queryset of Locations that are near our “geom” location.

Already we can find locations near other locations or arbitrary points! In Part II I’ll explain how to use Open Street Maps to turn a fuzzy query like “New York” into a point. And from there we can make a store locator!

Building an api for django activity stream with Generic Foreign Keys

I wanted to build a django-rest-framework api for interacting with django-activity-stream. Activity stream uses Generic Foreign Keys heavily which aren’t naturally supported. We can however reuse existing serializers and nest the data conditionally.

Here is a ModelSerializer for activity steam’s Action model.

from rest_framework import serializers
from actstream.models import Action
from myapp.models import ThingA, ThingB
from myapp.serializers import ThingASerializer, ThingBSerializer

class GenericRelatedField(serializers.Field):
    def to_representation(self, value):
        if isinstance(value, ThingA):
            return ThingASerializer(value).data
        if isinstance(value, ThingB):
            return ThingBSerializer(value).data
        # Not found - return string.
        return str(value)

class ActionSerializer(serializers.ModelSerializer):
    actor = GenericRelatedField(read_only=True)
    target = GenericRelatedField(read_only=True)
    action_object = GenericRelatedField(read_only=True)

    class Meta:
        model = Action

GenericRelatedField will check if the value is an instance of a known Model and assign it the appropriate serializer.

Next we can use a viewset for displaying Actions. Since activity stream uses querysets it’s pretty simple to integrate with a ModelViewSet. In my case I’m checking for a get parameter to determine whether we want all actions, actions of people the logged in user follows, or actions of the user. I added some filters on action and target content type too.

from rest_framework import viewsets
from actstream.models import user_stream, Action
from .serializers import ActionSerializer


class ActivityViewSet(viewsets.ReadOnlyModelViewSet):
    serializer_class = ActionSerializer

    def get_queryset(self):
        following = self.request.GET.get('following')
        if following and following != 'false' and following != '0':
            if following == 'myself':
                qs = user_stream(self.request.user, with_user_activity=True)
                return qs.filter(actor_object_id=self.request.user.id)
            else:  # Everyone else but me
                return user_stream(self.request.user)
        return Action.objects.all()

    filter_fields = (
        'actor_content_type', 'actor_content_type__model',
        'target_content_type', 'target_content_type__model',
    )

Here’s the end result, lots of nested data.
Screenshot from 2015-07-08 17:44:59

Adding new form in a formset

Everything I read about adding a new form to a formset with javascript involves cloning an existing form. This is a terrible method, what if the initial forms are 0? What about initial data? Here’s IMO better way to do it that uses empty_form, a function Django gives you to create a form where i is __prefix__ so you can easily replace it.

Add this under you “Add new FOO” button. In my case I have a question_form with many answers (answers_formset).

<script>
      var form_count_{{ question_form.prefix }} = {{ answers_formset.total_form_count }};
      $('#add_more_{{ question_form.prefix }}').click(function() {
          var form = '{{answers_formset.empty_form.as_custom|escapejs}}'.replace(/__prefix__/g, form_count_{{ question_form.prefix }});
          $('#answers_div_{{ question_form.prefix }}').append(form);
          form_count_{{ question_form.prefix }}++;
          $('#id_{{ answers_formset.prefix }}-TOTAL_FORMS').val(form_count_{{ question_form.prefix }});
      });
</script>

This creates you empty_form right in javascript, replaces the __prefix__ with the correct number and inserts it, in my case I made an answers_div. See empty_form.as_custom, you could just do empty_form but that would just give the you basic form html. I want custom html. Make a separate template for this. Here’s mine but this just an example.

{{ answer.non_field_errors }}
{% for hidden in answer.hidden_fields %} {{ hidden }} {% endfor %}
<table>
    <tr>
        <td>
            <span class="answer_span">{{ answer.answer }} {{ answer.answer.errors }}</span>
        </td>
        ......etc.......
    </tr>
</table>

In your original template you can add the forms like this {% include “omr/answer_form.html” with answer=answer %}
But for the as_custom you need to edit your form itself to add the function.

def as_custom(self):
    t = template.loader.get_template('answer_form.html')
    return t.render(Context({'answer': self},))

I find this method far more stable than trying to clone existing forms. It seems to play well with the javascript I have in some of my widgets. Clone on the other hand gave me tons of trouble and hacks needed to fix it.

Django get_or_default

Quick hack today. Often I find myself wanting to get some django object, but in the case it doesn’t exist default it to some value. Specially I keep my end user configurable settings in my database. Typically I set this up with initial data so all the settings are already there, but sometimes I’ll add a setting and forgot to add it on some site instance.

class Callable:
    def __init__(self, anycallable):
        self.__call__ = anycallable

def get_or_default(name, default=None):
        """ Get the config object or create it with a default. Always use this when gettings configs"""
        object, created = Configuration.objects.get_or_create(name=name)
        if created:
            object.value = default
            object.save()
        return object
    get_or_default = Callable(get_or_default)

Now I can safely call things like edit_all = Configuration.get_or_default(“Edit all fields”, “False”) which will return my configuration object with the value set as False if not specified. Much better than a 500 error. There are plenty of other uses for this type of logic. Get_or_return_none for example. The goal for me is to stop 500 errors from my own carelessness by having safe defaults.

Django admin: better export to XLS


The goal here is to make a slick gui for selecting exactly what the user wants to export from Django’s Change List view. It will be an global action, so lets start there.

def export_simple_selected_objects(modeladmin, request, queryset):
  selected_int = queryset.values_list('id', flat=True)
    selected = []
    for s in selected_int:
        selected.append(str(s))
  ct = ContentType.objects.get_for_model(queryset.model)
  return HttpResponseRedirect("/export_to_xls/?ct=%s&ids=%s" % (ct.pk, ",".join(selected)))
export_simple_selected_objects.short_description = "Export selected items to XLS"
admin.site.add_action(export_simple_selected_objects)

This adds a global action called Export selected items to XLS. I went with xls instead of ods because xlwt is very mature and LibreOffice can open xls just fine. It’s limited by the max length of get variables because it just lists each id. See this bug report. Next is the view.

import xlwt
def admin_export_xls(request):
    model_class = ContentType.objects.get(id=request.GET['ct']).model_class()
    queryset = model_class.objects.filter(pk__in=request.GET['ids'].split(','))
    model_fields = model_class._meta.fields

    if 'xls' in request.POST:
        workbook = xlwt.Workbook()
        worksheet = workbook.add_sheet(unicode(model_class._meta.verbose_name_plural))
        fields = []
        # Get selected fields from POST data
        for field in model_fields:
            if 'field__' + field.name in request.POST:
                fields.append(field)
        # Title
        for i, field in enumerate(fields):
            worksheet.write(0,i, field.verbose_name)
        for ri, row in enumerate(queryset): # For Row iterable, data row in the queryset
            for ci, field in enumerate(fields): # For Cell iterable, field, fields
                worksheet.write(ri+1, ci, unicode(getattr(row, field.name)))
        # Boring file handeling crap
        fd, fn = tempfile.mkstemp()
        os.close(fd)
        workbook.save(fn)
        fh = open(fn, 'rb')
        resp = fh.read()
        fh.close()
        response = HttpResponse(resp, mimetype='application/ms-excel')
        response['Content-Disposition'] = 'attachment; filename=%s.xls' % \
              (unicode(model_class._meta.verbose_name_plural),)
        return response

    return render_to_response('export_to_xls.html', {
        'model_name': model_class._meta.verbose_name,
        'fields': model_fields,
    }, RequestContext(request, {}),)

Remember to set up your URLs. Next is the HTML. Maybe something like this

<script type="text/javascript">
	$(document).ready(function()
	{
		$("#check_all").click(function()
		{
			var checked_status = this.checked;
			$(".check_field").each(function()
			{
				this.checked = checked_status;
			});
		});
	});
</script>
<h2> Export {{ model_name }} </h2>
    <form method="post" action="">
		<table>
			<tr>
				<th>
					<input type="checkbox" id="check_all" checked="checked" />
				</th>
				<th>
					Field
				</th>
			</tr>
			{% for field in fields %}
				<tr>
					<td>
						<input type="checkbox" class="check_field" checked="checked" name="field__{{ field.name }}"/>
					</td>
					<td>
						{{ field.verbose_name }}
					</td>
				</tr>
			{% endfor %}
		</table>
        <input type="submit" name="xls" value="Submit"/>
    </form>

The javascript just makes the check all box work. Note I use jquery, if you don’t you will need to rewrite it. Very simple but it works. Now users won’t have to delete unwanted columns from xls reports. Notice how the user is left on the export screen and not happily back to the edit list. Some ajax can solve this. I’m overriding the global change_list.html which actually isn’t ideal if you use any plugins that also override it. Here’s what I added.

<script src="/static/js/jquery.tools.min.js" type="text/javascript"></script>
<script type="text/javascript">
  $(document).ready(function()
  {
    $(".button").click(function()
    {
     if (
       $("option[value=export_simple_selected_objects]:selected").length
       && $("input:checked").length
     ) {
        $.post(
          "",
          $("#changelist-form").serialize(),
          function(data){
              $("#export_xls_form").html(data);
          }
        );
        $("#export_xls_form").overlay({
          top: 60
        });
        $("#export_xls_form").overlay().load();
        return false;
      }
    });
  });
</script>
<!-- Overlay, when you edit CSS, make sure this display is set to none initially -->
  <div class="modal" id="export_xls_form"><button class="close"> Close </button></div>

I use jquery tools overlay to make a nice overlay screen while keeping the user on the change list page. Basically I want a div to appear and then load some stuff from ajax. What’s cool is that I just post the data to “” so the regular Django admin functions work without editing them for AJAX. Well I did add to the submit button onclick=’$(“#export_xls_form”).overlay().close();’ to close the window when submitting. Ok I’m a complete liar I also added get_variables = request.META[‘QUERY_STRING’] to the view as a cheap way to keep those GET variables. But hey it’s still works as a non ajax admin action and that’s cool.

In the screenshot I added a CSS3 shadow and rounded corners to make it look better.

What’s next? Well it would be nice if we could access foreign key fields. If this had some type of advanced search and saving mechanism, we’d have a full generic Django query builder. Hmm.

Spreadsheet reporting in Django

It’s often desired to quickly export data in a generic method that takes little coding. There are already some solutions for this. I didn’t like any however as they ignore  many to many fields. One could argue a more robust system might be needed when handling more complex reports with gui sql query builders and such. Screw that here’s a hack to get the job 95% done.

I want to check off fields in Django Admin’s model list display. Then make an action to make a spreadsheet report, not some crap csv.

Here is my hack function to take a model and query set and spit out an .xls file

def admin_export_xls(request, app, model, qs=None):
    """ Exports a model to xls.
    qs: optional queryset if only exporting some data"""
    mc = ContentType.objects.get(app_label=app, model=model).model_class()
    wb = xlwt.Workbook()
    ws = wb.add_sheet(unicode(mc._meta.verbose_name_plural))
    #for i, f in enumerate(mc._meta.fields):
    #    ws.write(0,i, f.name)
    # Lets get all fields names, even m2m
    for i, field in enumerate(mc._meta.get_all_field_names()):
        ws.write(0,i, field)
    if not qs:
        qs = mc.objects.all()

    for ri, row in enumerate(qs):
        for ci, f in enumerate(mc._meta.get_all_field_names()):
            try:
                # terrible way to detect m2m manager
                if unicode(getattr(row, f))[1:51] == 'django.db.models.fields.related.ManyRelatedManager':
                    # If it's a M2M relationship, serialize it and throw it in one cell.
                    value = ""
                    for item in getattr(row, f).all():
                        value += unicode(item) + ", "
                    value = value[:-2]
                    ws.write(ri+1, ci, value)
                else:
                    ws.write(ri+1, ci, unicode(getattr(row, f)))
            except:
                # happens when the m2m is has an appended _set. This is a hack that works sometimes, it sucks I know
                try:
                    f += "_set"
                    value = ""
                    for item in getattr(row, f).all():
                        value += unicode(item) + ", "
                    value = value[:-2]
                    ws.write(ri+1, ci, value)
                except:
                    ws.write(ri+1, ci, "")
    fd, fn = tempfile.mkstemp()
    os.close(fd)
    wb.save(fn)
    fh = open(fn, 'rb')
    resp = fh.read()
    fh.close()
    response = HttpResponse(resp, mimetype='application/ms-excel')
    response['Content-Disposition'] = 'attachment; filename=%s.xls' % \
          (unicode(mc._meta.verbose_name_plural),)
    return response

That will serialize manytomany fields to comma separated fields all in one cell. Next in you need an admin aciton.

def export_selected_objects(modeladmin, request, queryset):
    app = queryset[0]._meta.app_label
    model = queryset[0]._meta.module_name
    return admin_export_xls(request, app, model, queryset)
export_selected_objects.short_description = "Export selected items to XLS"

You can see I made mine global. It works on all models. For this to work well you need to make sure your unicode representations of your models are useful. The most common thing I run into are phone numbers. A person can have unlimited phone numbers. The end user will assume a report of people will include this. I make my number’s unicode something like “Cell: 555-555-5555”.

Of course this code isn’t perfect and there are many times a more robust solution will be needed. What if you want a report of companies with contacts at the company and phone numbers of each contact. At that point you need to generate a query that can get such data and that’s going to take some gross gui query builder program or custom reports by you the developer.

More uno reports

I’ve been playing around more with openoffice.org’s uno api for making reports. Since I’ll be making more updates I’ll just post a link to the Google Code site

http://code.google.com/p/student-worker-relational-database/source/browse/#svn/trunk/ecwsp/uno_report

My latest improvement is supporting tables. It’s probably best to just show what it does.

Now a user could just download the report, change fonts, layout, etc, and reupload it. It does have it’s limitations and is a work in progress but it’s already pretty cool. As a developer I can just define what variables can be used and let someone else make the report (and change it around later). To use it you just have to make the proper data structures, so really this could be ported to any data driven application, not just Django.

Django and OpenOffice.org uno reports

Openoffice.org’s python uno library is great for report writing in a web application. I wanted to create a report template system where users can create templates with a word processor of their choice. I created a simple template model which consists of a CharField and FileField. Users can upload templates here in .doc, .odt, or any format OpenOffice.org supports.

Next I created some generic template report functions.


from django.http import HttpResponse
from django.core.servers.basehttp import FileWrapper

import uno
import os
import string
import tempfile

def findandreplace(document, search, find, replace):
"""This function searches and replaces. Create search, call function findFirst, and finally replace what we found."""
#What to search for
search.SearchString = unicode(find)
#search.SearchCaseSensitive = True
#search.SearchWords = True
found = document.findFirst( search )
if found:
print 'Found %s' % find
while found:
found.String = string.replace( found.String, unicode(find),unicode(replace))
found = document.findNext( found.End, search)

def replace_report(infile, outfile, data):
"""Replace words in a file use like this
data={}
data['$TEST']='worked yay'
returns a django HttpResponse of the file"""
# Boring uno stuff
local = uno.getComponentContext()
resolver = local.ServiceManager.createInstanceWithContext("com.sun.star.bridge.UnoUrlResolver", local)
context = resolver.resolve("uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext")
desktop = context.ServiceManager.createInstanceWithContext("com.sun.star.frame.Desktop", context)

# open document
document = desktop.loadComponentFromURL("file://" + str(infile) ,"_blank", 0, ())
cursor = document.Text.createTextCursor()

search = document.createSearchDescriptor()
#Do a loop of the data and replace the content.
for find,replace in data.items():
findandreplace(document,search,unicode(find),unicode(replace))
print find,replace

# create temporariy file to store document in
tmp = tempfile.NamedTemporaryFile()
document.storeToURL("file://" + str(tmp.name), ())
document.dispose()

# create http response out of temporariy file.
wrapper = FileWrapper(file(tmp.name))
response = HttpResponse(wrapper, content_type='application/odt')
response['Content-Length'] = os.path.getsize(tmp.name)
response['Content-Disposition'] = 'attachment; filename=' + outfile

return response

In this example I’m replacing text in a file. The result is returned as a http response. I can use it like this


template = Template.objects.get_or_create(name="My Selected Template")[0]
data={}
data['$date'] = str(date.today())
return replace_report(template.file.path, "file.odt", data)

In this case I am taking out my template and replacing all instances of the word “$date” with the current date. I choose to use $ to mark it as a variable but it could really be anything. Now when a user wants to change a font they can do it themselves.

Django models from non Django applications

I have an existing LAMP application that I want to write some quick code for. I want to easily manipulate the database without taking the time to understand how the application works fully and mess around with PHP. The solution, create a Django model to act as a wrapper for the sections of the database I need.

First I created some very simple views in MySQL to my new Django created database. I could have just put both programs in the same database but this should make updates easier. Remember in MySQL views are updatable only when the view references one table. All my views look like
create otherdb.my_view as select * from newdb.some_table;
With this method I can easily drop and replace the other database when I update it.

Next I run Django’s inspectdb command to get some generic Django model code. Edit this to one’s liking. For me that meant renaming fields, deleting the primary key (so Django automatically makes it. Only works if it is in fact a primary key auto incr integer). Turn foreign keys into foreign key fields. Make dates into DateFields.

Setup my Django admin interface and all the sudden I can use the spiffy Django admin interface on a completely different application. YAY!

However I ran into a small problem. I’ll post this separately for people who only care about Creating a custom Django DateTimeField

Introducing SWORD

Student WOrker Relational Database (SWORD) is a student worker database for use in schools with work study programs. It’s the project I’ve been working on for the past 6 months or so.

http://code.google.com/p/student-worker-relational-database/

It’s a great example of leveraging Django to create a full website in very little time (Until recently it was just a side project I worked on occasionally) . I’m releasing it open source to help distribute it for use in other schools. Right now the installation process is a little ugly though. I’ll have to learn how to make Debian  and RPM packages sooner or later. Next steps will be to integrate it with some other programs, possibly Schooltool and  SugarCRM. It’s already working with CAS for Single Sign On. If you know any schools that might be able to use this please comment or email. I may be offering it as a hosted solution soon.