I have this query:

def floodhazard_tbl(request):
    if request.method == "GET":
        # create a list
        to_json = []

        # this code is results a messy JSON data that need underscore.js to manipulate
        # in order for us to use datatables
        getgeom1 = FloodHazard.objects.get(id=3).geom
        response_high = list(PolyStructures.objects.filter(geom__within=getgeom1).values(
            'brgy_locat', 'municipali').annotate(counthigh=Count('brgy_locat')))

        getgeom2 = FloodHazard.objects.get(id=2).geom
        response_medium = list(PolyStructures.objects.filter(geom__within=getgeom2).values(
            'brgy_locat', 'municipali').annotate(countmedium=Count('brgy_locat')))

        getgeom3 = FloodHazard.objects.get(id=1).geom
        response_low = list(PolyStructures.objects.filter(geom__within=getgeom3).values(
            'brgy_locat', 'municipali').annotate(countlow=Count('brgy_locat')))

        return StreamingHttpResponse(list(json.dumps(to_json)), content_type='application/json')

Although it works, the respond is very slow and I wonder why.

Here's my model:

class PolyStructures(models.Model):
    bldg_name = models.CharField(max_length=100)
    block_name = models.CharField(max_length=75)
    bldg_type = models.CharField(max_length=50)
    brgy_locat = models.CharField(max_length=50)
    municipali = models.CharField(max_length=50)
    province = models.CharField(max_length=50)
    geom = models.MultiPolygonField(srid=32651, null=True, blank=True)
    objects = models.GeoManager()

    def __unicode__(self):
        return self.bldg_name

class FloodHazard(models.Model):
    hazard = models.CharField(max_length=6)
    date_field = models.DateTimeField(null=True, blank=True)
    geom = models.MultiPolygonField(srid=32651, null=True, blank=True)
    objects = models.GeoManager()

    def __unicode__(self):
        return self.hazard

Here's my JS function for displaying the result in my template:

function e() {
            url: "tbl/",
            dataType: "json",
            beforeSend: function () {
                $("#info_tbl").show().text("Getting data from server...Please wait.").addClass("getme")
            success: function (e) {
                function t(e) {
                    return _.reduce(e, function (e, t) {
                        return e + parseFloat(t || 0)
                    }, 0)
                $("#info_tbl").text("Successfully Loaded!").fadeOut(1e3).removeClass("getme"), $("#myErrorWrapper").hide(), $("#table_wrapper").show();
                var a = _.chain(e).flatten().groupBy("brgy_locat", "municipali").map(function (e, r) {
                    return {
                        municipality: _(e).chain().pluck("municipali").first(),
                        brgy: r,
                        low: t(_(e).chain().pluck("countlow").value()),
                        medium: t(_(e).chain().pluck("countmedium").value()),
                        high: t(_(e).chain().pluck("counthigh").value())
                for (var o = 0; o < a.length; o++) r.fnAddData([a[o].municipality, a[o].brgy, a[o].low || 0, a[o].medium || 0, a[o].high || 0])
            error: function () {
                e(), $("#myErrorWrapper").show()

BTW, I'm using underscore.js and datatables plug-in.

The shapefile that I loaded in the PostgreSQL database has a size of 18mb and take up 3 rows in my table but before it was 28K+ rows, my co-worker "dissolved" it and make it in 3 (Low, Medium, High) rows.

I am using django-debug-toolbar to measure the query time.

Is there any way to make it fast? Or is it because of the shapefile size? or my query?


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.