1
\$\begingroup\$

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')))
        to_json.append(response_high)

        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')))
        to_json.append(response_medium)

        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')))
        to_json.append(response_low)

        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() {
        $.ajax({
            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())
                    }
                }).value();
                r.fnClearTable();
                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?

\$\endgroup\$

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.